How to automate ImportJSON function in Google Sheets

Question

I am a beginner in terms of using Google Scripts/any coding languages but could really do with some help in automating data import from the Environment Agency Real-Time flood monitoring API. The API addresses are below:

https://environment.data.gov.uk/flood-monitoring/id/stations/023003/readings?latest https://environment.data.gov.uk/flood-monitoring/id/stations/023004/readings?latest https://environment.data.gov.uk/flood-monitoring/id/stations/023020/readings?latest

I need to schedule a trigger to refresh the information I import every 10-15 minutes. Therefore, I need to script my API import. I have tried using the following IMPORTJSON code from https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs

However, from what I understand, this is a generic code which allows me to use the IMPORTJSON function within Google Sheets. I need an additional function which allows me to schedule a trigger for every 10-15 minutes to refresh the information within my Google Sheet. I have also tried scripting to parse the JSON API but the items array is causing difficulties. This is the script I currently have:

function FloodEWS() {

var res = UrlFetchApp.fetch(“https://environment.data.gov.uk/flood-monitoring/id/stations/023004/readings.json?latest"); 
var content = res.getContentText();
var json = JSON.parse(content);
  var context = json["@context"];
  var meta = json["meta"];
  var items = json["items"]
  Logger.log(items);
}

The API I am trying to parse is below - the "value" and "dateTime" is what I need to extract from the items array (and have automated):

{ 
  "@context" : "http://environment.data.gov.uk/flood-monitoring/meta/context.jsonld" ,
  "meta" : { 
    "publisher" : "Environment Agency" ,
    "licence" : "http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" ,
    "documentation" : "http://environment.data.gov.uk/flood-monitoring/doc/reference" ,
    "version" : "0.9" ,
    "comment" : "Status: Beta service" ,
    "hasFormat" : [ "http://environment.data.gov.uk/flood-monitoring/id/stations/023003/readings.csv?latest", "http://environment.data.gov.uk/flood-monitoring/id/stations/023003/readings.rdf?latest", "http://environment.data.gov.uk/flood-monitoring/id/stations/023003/readings.ttl?latest", "http://environment.data.gov.uk/flood-monitoring/id/stations/023003/readings.html?latest" ]
  }
   ,
  "items" : [ { 
    "@id" : "http://environment.data.gov.uk/flood-monitoring/data/readings/023003-level-stage-i-15_min-m/2020-08-26T09-15-00Z" ,
    "dateTime" : "2020-08-26T09:15:00Z" ,
    "measure" : "http://environment.data.gov.uk/flood-monitoring/id/measures/023003-level-stage-i-15_min-m" ,
    "value" : 1.68
  }
 ]

Any help would be really appreciated.

Answer 1

Solution:

You can still use the ImportJSON function provided by this script and save it to a separate google script in the script editor.

Then you can use the following function to paste the ImportJSON formulas for all the URLs to Sheet1:

function updateData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  
  sh.getRange(1,1,sh.getMaxRows(),sh.getMaxColumns()).clearContent();
  
  const urls = ["https://environment.data.gov.uk/flood-monitoring/id/stations/023003/readings?latest",
              "https://environment.data.gov.uk/flood-monitoring/id/stations/023004/readings?latest",
              "https://environment.data.gov.uk/flood-monitoring/id/stations/023020/readings?latest"];
  
  urls.forEach(url=>{
               
               sh.getRange(sh.getLastRow()+1,1,1,1).setValue(`=ImportJSON("${url}")`);
               SpreadsheetApp.flush();
             }
)
}

In order to setup a 10 minutes time-trigger for updateData() you can execute the following code once:

function create_Trigger(){
ScriptApp.newTrigger("updateData")
  .timeBased()
  .everyMinutes(10)
  .create();
}

Result: