CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetshttp-status-codes
Matt
Matt

Reputation: 383

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

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

Upvotes: 0

Views: 119

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

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()
}

References:

Upvotes: 0

doubleunary
doubleunary

Reputation: 19065

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.

Upvotes: 0

Related Questions