Using Google Sheets, how do I run a script every hour and populate each cell in a column?

Question

I'm using google sheets and have never run a "cron job" here. To give credit where it's due, I found this awesome code to help me access the speed insights api here: https://statsravingmad.com/measure/page-speed-insights/

function speed(url,device,filter_third_party_resources,http_secure) {

  url = url || 'www.statsravingmad.com'; 
  strategy = 'desktop' || device; 
  filter_third_party_resources = 'true' || filter_third_party_resources;
  http_secure = 'false' || http_secure ; 

  switch (http_secure)  {
    case 'false':
    http_protocol = 'http://';
    break;
    case 'true':
    http_protocol = 'https://';
    break;
 }

  var key = 'api-key';  
  var api = 'https://www.googleapis.com/pagespeedonline/v2/runPagespeed?url=' + http_protocol + url
  + '&filter_third_party_resources=' + filter_third_party_resources + 
'&strategy=' + strategy + '&key=' + key;

  var response = UrlFetchApp.fetch(api, {muteHttpExceptions: true });

  var result = JSON.parse(response.getContentText());    

    score = result.ruleGroups.SPEED.score;

    return(score);
}

So I have this code in a function that is triggered every hour for my particular test sites in my google sheet.

But, the data is only filling one cell per site, the cell that the formula is assigned to.

When using google sheets, how can I modify this in order to have it fill a new cell in a column every hour? Do I modify this code, do I have to set up another function, or is there an option to fill the cells down a column?

Unknown Unknown · Accepted Answer · 2017-09-17T05:02:11

This function can be modified to write, say, to column C of Sheet1. Here is how it would end, instead of return(score) (there is no need to return anything if the value is written to the spreadsheet directly; the function would not be invoked from the spreadsheet, but from a trigger).

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var values = sheet.getRange("C:C").getValues();  // using column C
  var lastRow = 1;
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] != "") {
      lastRow = i + 1;
    }
  }
  sheet.getRange(lastRow + 1, 3).setValue(score);  // column = 3 because C

Here the loop finds the last row in column C that has data, and the values of score is placed under it.

Using Google Sheets, how do I run a script every hour and populate each cell in a column?

1 Answers