visually display status when looping through google sheets data

show a processing status in a cell when looping through rows in google sheets using google apps script.

visually display status when looping through google sheets data

context

when working with large set of data in a google sheet that requires you to run computation on each row, google apps script typically (automatically) batches them and then processes it — and some times, you don't get to see the end result until either the entire script / data is processed or if the script throws an error (or even times out).

even if the script errors out, you'd most times still get to see the result for whichever row was previously processed but in a few anecdotal instances, i've seen the data not log into google sheets at all and so, i started forcing the script to print the result, as and when the row gets processed.

prior reading

  • SpreadsheetApp.flush() - reference here

codebase

you can make a copy of this google sheet or adapt the code from below -

function loop() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const baseUrl = 'https://api.hashify.net/hash/md5/hex?value=';
  const [header, ...data] = ss.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    let row = data[i];
    let keyword = row[header.indexOf("Keyword")];
    ss.getRange(1 + i + 1, header.indexOf("Status") + 1).setValue("Fetching...");
    SpreadsheetApp.flush();
    let response = UrlFetchApp.fetch(baseUrl + keyword);
    let hash = JSON.parse(response.getContentText()).Digest;
    ss.getRange(1 + i + 1, header.indexOf("MD5 hash") + 1).setValue(hash);
    ss.getRange(1 + i + 1, header.indexOf("Status") + 1).setValue("Done");
    SpreadsheetApp.flush();
  }
}

demo