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

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

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';
  }

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.