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
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.