I'm making a sheet that imports a large amount of data from a site. I want to do this slowly so as not to clog up the site.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[3]; 

  for (var i=1; i<39 ; i++){
    var cell = sheet.getRange(1, (i*2)-1);
    cell.setValue(=QUERY(IMPORTHTML("WEBSITE","table",table_number), "QUERY STUFF",0));

All this does it make it dump everything once the loop is done executing. So all IMPORTHTMLs go off at once. The opposite of what I'd like.

I'd like the sheet to update the cell value as it loops, and wait for the data to import (maybe 10 seconds) before going to the next iteration. Is this possible?


SpreadsheetApp.flush(); mid-loop accomplishes my goals.

  • While setValue is working for you, it worth to note that there is a specfic method to add a formula: setFormula
    – Rubén
    Apr 26 '19 at 6:36
  • @Rubén Move this comment to below the question?
    – user55949
    Sep 23 '19 at 12:26
  • @JanDoggen Here is fine
    – Rubén
    Sep 23 '19 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.