Skip to content

WebNinjaDeveloper.com

Programming Tutorials




Menu
  • Home
  • Youtube Channel
  • PDF Invoice Generator
Menu

Google Sheets Web App Importing Live Data from API Using Google Apps Script Example

Posted on May 16, 2023

 

 

Welcome folks today in this blog post we will be using the google apps script to import live data from the api inside the google sheets as shown below

 

 

Get Started

 

 

In order to get started you need to make a code.gs file and copy paste the following code

 

 

code.gs

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
function main(){
    
    // Set up the parameters  and variables
    var sheetName = '<name>'; // The name of the sheet (not the Spreadsheet) we want to write the data e.g Sheet1
    var tableId = '<table id>'; // The id of the view to query the data from e.g ga:123456
    var startDate = 'yyyy-MM-dd'; // The start date of the query with the appropriate format e.g 2018-04-01 (1 April 2018)
    var endDate = 'yyyy-MM-dd'; // The end date of the query with the appropriate format e.g 2018-04-30 (30 April 2018)
  
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName(sheetName);
  
 
    // Set Up the query arguments
    var metrics = ['ga:pageviews,ga:avgTimeOnPage,ga:bounceRate'];
    var options = {
        'dimensions': 'ga:pagePath',
        //'filters': '',
        'sort': '-ga:pageviews',
        //'segment': '',
        'samplingLevel': 'HIGHER_PRECISION',
        'max-results': '5' // To limit the results to 5. Maximum number of results: 10000
    }
 
    // Fetch the report
    var report = gaGet(tableId, startDate, endDate, metrics, options);
    var data = report.rows;
 
    // Get the range to write and write the results
    var writeRange = sheet.getRange(1, 1, data.length, data[0].length) // Read reference for getRange arguments
    writeRange.setValues(data);
 
}
 
function gaGet(tableId, startDate, endDate, metrics, options) {
    // Apply standard options
    options = options || {};
    options['max-results'] = options['max-results'] || '10000';
    // If errors persist up to 5 times then terminate the program.
    for (var i = 0; i < 5; i++) {
        try {
          return Analytics.Data.Ga.get(tableId, startDate, endDate, metrics, options); // 503
        } catch (err) {
          // https://developers.google.com/analytics/devguides/reporting/core/v3/coreErrors
          if (err.message.indexOf('a server error occurred') > -1) {
            Logger.log('Backend Error');
            // Note: Don't listen to Google's reply and retry request after 2 minutes
            Utilities.sleep(2 * 60 * 1000);
          } else if (err.message.indexOf('User Rate') > -1) {
            Logger.log('Rate Limit Error');
            // Exponential Backoff
            Utilities.sleep(1000 * Math.pow((i + 1), 2));
          } else if (err.message.indexOf('too many concurrent connections') > -1) {
            Logger.log('Concurrent Connections Error');
            // Exponential Backoff
            Utilities.sleep(1000 * Math.pow((i + 1), 2));
          } else {
            Logger.log(err);
            throw err;
          }
        }
    }
    throw 'Error. Max retries reached';
  }

 

 

Now in the above code we need to replace the sheet name and also the date as well.

 

Recent Posts

  • Node.js Express Project to Remove Background of Images Using Rembg & Formidable Library in Browser
  • Node.js Tutorial to Remove Background From Image Using Rembg & Sharp Library in Command Line
  • Python 3 Flask Project to Remove Background of Multiple Images Using Rembg Library in Browser
  • Python 3 Rembg Library Script to Bulk Process Multiple Images and Remove Background in Command Line
  • Python 3 Rembg Library Script to Remove Background From Image in Command Line
  • Angular
  • Bunjs
  • C#
  • Deno
  • django
  • Electronjs
  • java
  • javascript
  • Koajs
  • kotlin
  • Laravel
  • meteorjs
  • Nestjs
  • Nextjs
  • Nodejs
  • PHP
  • Python
  • React
  • ReactNative
  • Svelte
  • Tutorials
  • Vuejs




©2023 WebNinjaDeveloper.com | Design: Newspaperly WordPress Theme