How to drip-feed/schedule a Google Sheets script for http status on multiple urls

Question

I have a google sheets script setup that checks multiple urls for their http server status.

function getStatusCode(url){
   var options = {
     'muteHttpExceptions': true,
     'followRedirects': false
   };
   var url_trimmed = url.trim();
   var response = UrlFetchApp.fetch(url_trimmed, options);
   return response.getResponseCode();
}

Then in my spreadsheet I have:

___________ A ___________| __________ B __________ |

www.mysite.com/mypage1 __| "=getStatusCode(A1)"

www.mysite.com/mypage2 __| "=getStatusCode(A2)"

and so on...

A the moment, the script runs on all cells as soon as I open the sheet (and there's lots of them). Is there any way on arranging this so it performs the script on a handful of cells on a schedule/limit of my choosing? The most ideal option would be to tell it to run the script on cell A1 then pause for a specified amount of time before running it on cell A2 etc..

The problem is that a custom function must finish execution within 30 seconds else it will fail. UrlFetchApp is slow and sleeping between calls will add to the problem. Would running this on a button click instead of onOpen be an acceptable solution? — Rafa Guillermo
– Rafa Guillermo, Commented Apr 5, 2021 at 14:52
Alternatively you could run the function on a minute trigger and update only one cell per minute? — Rafa Guillermo
– Rafa Guillermo, Commented Apr 5, 2021 at 14:53
@Rafa Yes a button click would be acceptable. How could I implement this? — Matt
– Matt, Commented Apr 5, 2021 at 17:22
@RafaGuillermo That would be perfect. I just don't know how to set this up. Would I need to edit the script first before setting up a trigger? (as the script doesn't specify any cells/range) — Matt
– Matt, Commented Apr 6, 2021 at 8:24
How many rows do you have? That will determine whether a button or a trigger would be better suited. — Rafa Guillermo
– Rafa Guillermo, Commented Apr 6, 2021 at 13:54

doubleunary · Accepted Answer · 2021-04-05 12:43:02Z

0

You can minimize the number of calls to external endpoints by utilizing the Cache Service. You should probably also make your custom function work with arrays so that it can fill the whole column with one call like =getStatusCode(A1:A). See Google's custom function optimization page for more info.

answered Apr 5, 2021 at 12:43

doubleunary

21.9k13 gold badges38 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

I don't really want to cache the results as I need real live results at the time. I want to focus more on a time delay per x number of rows kind of solution. If that is possible. To help with limiting the number of calls I could utilise more columns if needed (i.e. 1 column per batch).

doubleunary Over a year ago

You can use a short cache expiration time if you are concerned about outdated results — that would probably get you fresher results than sleeping between calls. In any case, the second bit about working with arrays will probably have more impact.

Nimantha · Accepted Answer · 2021-11-04 08:05:50Z

Answer:

You can use a minute trigger to run one query per minute, using PropertiesService to store how far you've gone through the sheet.

More Information:

PropertiesService is a class which allows you to store information within a script so that it can be accessed on each run. This means you can retain information/variables across runs, even after the script has finished execution.

The idea is as such:

Create a property store which saves the last row that the script ran on
On each run, check the property store. If there is a last row saved, run the script on the next row, and save the new last row to the property store.
If the current row is empty, assume we have hit the end of the sheet and delete the property store.
If there is no property store, start from the beginning of the sheet again
Run the script once per minute, so each status code retrieval will be staggered

Code Example:

// Copyright 2021 Google LLC.
// SPDX-License-Identifier: Apache-2.0

// Define your sheet so it can be accessed from all functions
const ss = SpreadsheetApp.getActiveSpreadsheet()
// Don't forget to change your sheet name:
const sheet = ss.getSheetByName("Sheet1")

// This is the function to run on minute trigger
function onMinuteTrigger() {
  // Define the property service and check if there is a last row saved
  const sp = PropertiesService.getScriptProperties()
  let row = sp.getProperty("last")

  // if there was no last row, set the 'row' variable to 0
  if (!row) row = 0

  // add 1 to row so the script runs on the next row, not the last
  row++
  
  // call the status code function
  const statusCode = getStatusCode(row)
  // if the statusCode is null then the row was blank; 
  // so delete the property store and return
  if (statusCode == null) {
    sp.deleteProperty("last")
    return
  }

  // if the script has got this far there was a returned status code
  // save the status code to the sheet and save the new last row
  // to the property store
  sheet.getRange(row, 2).setValue(statusCode)
  sp.setProperty("last", row)
}

Along with a slightly modified version of your statusCode() method:

// modified statusCode function from your question
// pass to it the row to update
function getStatusCode(row) {
  const options = {
    'muteHttpExceptions': true,
    'followRedirects': false
  }
  // manually get the url from the passed row variable
  const url_trimmed = sheet.getRange(row, 1).getValue().trim()

  // if url_trimmed is blank then the row is empty, return null
  if (url_trimmed.length == 0) return null
  
  // fetch the URL and return the response code
  const response = UrlFetchApp.fetch(url_trimmed, options)
  return response.getResponseCode()
}

Collectives™ on Stack Overflow

How to drip-feed/schedule a Google Sheets script for http status on multiple urls

2 Answers 2

2 Comments

Answer:

More Information:

Code Example:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Answer:

More Information:

Code Example:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related