CODEWITHSUNDEEP
google-apps-script
Nate Shanklin
Nate Shanklin

Reputation: 11

Script to open a long list of URLs one at a time every 5 seconds and then open the next URL

I have a google spreadsheet with a large number of API Urls.

They look like this => http://oasis.caiso.com/oasisapi/SingleZip?resultformat=6&queryname=PRC_LMP&version=1&startdatetime=20160101T08:00-0000&enddatetime=20160103T08:00-0000&market_run_id=DAM&grp_type=ALL

The database I am drawing from limits requests to one every 5 seconds. When you follow the link it will download a zip file with cvs files.

I would like to write a script that will follow a URL, wait 6 seconds and then move on to the next URL on the list.

I would like it to stop when it gets to the last URL

I am imagining that I would need to use a "when" loop, but I cannot figure out how to install a wait period, or how to get it to open the URL.

HELP!!!!

I tried a batch URL follow, which failed because of the timing issue. I began to write the When loop, but I am totally stuck.

I would like to run through the huge list of links fully once. To date I cannot make anything work.

function flink(){
var app = spreasheetapp
//access the current open sheet
var activesheet = app.getactivespreadsheet().getactivesheet()
var activecell= activesheet.getrange(11,11).openurl
//I am getting totally stuck here

I have tried using an iterator but I have no idea how to add the time delay and then I cannot seem to get the syntax for the iterator correct.

Upvotes: 1

Views: 671

Answers (2)

user12308052
user12308052

Reputation:

To access a url from AppsScript, you can use UrlFetchApp.fetch(url).

To force the script to wait for a certain amount of time, you can use Utilities.sleep(milliseconds).

  • References:

https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String) https://developers.google.com/apps-script/reference/utilities/utilities#sleepmilliseconds

Upvotes: 1

Iamblichus
Iamblichus

Reputation: 19309

I came up with this code that accesses each URL in the sheet. I added some comments so you can see what the script is doing step by step:

function accessURLs() {
  // Copy urls
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Links up to today");
  // Copy the contents of the url to another column removing the formulas (your script will appreciate this)
  sheet.getRange("C3:C").copyTo(sheet.getRange("D3:D"), {contentsOnly:true});
  var column = 4; // URLs are in column 4
  var row = 3; // URLs start at row 3
  var url = sheet.getRange(row, column).getValue();
  var responses = []
  // Loop through all urls in the sheet until it finds a cell that is blank (no more urls left)
  do {
    var options = {
      muteHttpExceptions: true
    }
    var response = UrlFetchApp.fetch(url, options); // Script makes a request to the url
    responses.push(response); // The latest response is added to the responses array
    Utilities.sleep(6000); // The script stops for 6 seconds
    row++;
    url = sheet.getRange(row, column).getValue();
  } while (url != ""); // Cell is not blank
}

Take into account that if we are to access ~1400 URLs and the script stops for 6 seconds after each fetch, it will take more than 2 hours to access all URLs.

Also, take into account that this script is just getting the data coming from the URL requests (it's stored in the variable responses), but it is not doing anything else. Depending on what you want to do with this data, you might want to add some extra stuff there.

I hope this is of any help.

Upvotes: 0

Related Questions