Concatenating an API URI with values from a Google Sheet-CodePudding

Currently, I am working with a google sheet using gscript, and I am trying to input data into the cells based on the value within column A of the sheet. The data is pulled through fetching the URI and I am trying to concatenate the URI with the ID located in Column A of the Google Sheet.

 var res = UrlFetchApp.fetch("MYAPI   JOBID   TOKEN");
  

  var content = res.getContentText();

  var json = JSON.parse(content);

  Logger.log(content)

  // Users Name //
  

  var dispatches = json["dispatches"]["items"];

  var lastItem = dispatches.pop();

  var dName = lastItem.vehicle.driver.contact.name

  Logger.log(dName);

  var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,3).setValue([dName]);

 // Pickup and Drop Off Information // 
 var task1timeframeBy = json["tasks"]["items"][0]["timeframe"]["by"]
 var task3timeframeBy = json["tasks"]["items"][2]["timeframe"]["by"]


 var task1Date = new Date(task1timeframeBy).toDateString()
 var task3Date = new Date(task3timeframeBy).toDateString()

 var task1Time = new Date(task1timeframeBy).toTimeString()
 var task3Time = new Date(task3timeframeBy).toTimeString()

 Logger.log(task1Date)
 Logger.log(task3Date)

  Logger.log(task1Time)
 Logger.log(task3Time)


 // Address Information //

var task1Address = json["tasks"]["items"][0]["zone"]["address"]
var task1Longitude = json["tasks"]["items"][0]["zone"]["coords"]["lon"]
var task1Latitude = json["tasks"]["items"][0]["zone"]["coords"]["lat"]

var task3Address = json["tasks"]["items"][2]["zone"]["address"]
var task3Latitude = json["tasks"]["items"][2]["zone"]["coords"]["lat"]
var task3Longitude = json["tasks"]["items"][2]["zone"]["coords"]["lon"]

Logger.log(task1Address)
Logger.log(task1Longitude)
Logger.log(task1Latitude)

Logger.log(task3Address)
Logger.log(task3Longitude)
Logger.log(task3Latitude)

 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,4).setValue([task1Latitude]);

 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,5).setValue([task1Longitude]);

 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,6).setValue([task3Latitude]);

 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,7).setValue([task3Longitude]);

  var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,8).setValue([task1Time]);

  var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(2,9).setValue([task3Time]);

  } }

The values located in column A are supposed to concatenate with the URI to pull different values and loop through the cells for input.

What I can not figure out is how to concatenate the URI with a variable, or loop through the cells to input the data requested. Can anyone help?

CodePudding user response：

There's certainly better ways, but since the core of your question seems to be about iterating through column A data and builind the URL, here's one (food for thought)... It's a bit hard without more information on your context, but this would be the approach I'd take:

const data = SpreadsheetApp.getSheetByName('SheetName').getRange("A2:A").getValues();

let JOBID = '';
let APIdata = [];
for (let a = 0; a < data.length; a  ) {
    JOBID  = data[a]
    URL = "MYAPI"   JOBID   "TOKEN"
    //Do the API call, but you'd have to check for their quota and set an interval maybe to meet their demand. 
    //Store the data into an array, so you can place the data into the defined range later
}