Search code examples
google-apps-scriptgoogle-sheets

api calls with user id's in a range

I have this code that does an api call and writes it into a google sheet. I would like to have a range where I can have several id's and run the calls after each other so I don't have to do the calls one by one and change the id manually.

There is a spreadsheet here which has the range and an output sheet.

And the code I have been using so far is this

function callNumbers() {
   
   // include the API Key
const API_KEY = 'xyz';
 
// set the endpoint
const url = 'https://www.example.com/wp-json/armember/v1/member_details?arm_api_key=xyz&user_id=19&metakeys=country,checkbox_vgc5x';
 
// call the API
const response = UrlFetchApp.fetch(url + '&api_key=' + API_KEY);
  Logger.log(response.getContentText());
   
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);
   
}
Solution

  • Although I'm not sure whether I could correctly understand your expected result when your provided Spreadsheet is used, how about the following modified scripts?

    Modified script 1:

    In this modification, all requests are run with the fetchAll method.

    function callNumbers1() {
  const API_KEY = 'xyz';
  const srcSheetName = "Range of Id's";
  const dstSheetName = "user data output";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const ids = srcSheet.getRange("A1:A" + srcSheet.getLastRow()).getDisplayValues();
  const reqs = ids.map(([id]) => ({ url: `https://###/wp-json/armember/v1/member_details?arm_api_key=xyz&user_id=${id}19&metakeys=country,checkbox_vgc5x&api_key=${API_KEY}`, muteHttpExceptions: true }));
  const res = UrlFetchApp.fetchAll(reqs);
  const dstValues = res.map(r => [r.getResponseCode() == 200 ? r.getContentText() : null]);
  const dstSheet = ss.getSheetByName(dstSheetName);
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, dstValues.length).setValues(dstValues);
}
    • When this script is run, the IDs are retrieved from column "A" of the source sheet. And, the retrieved values are put into column "A" of the destination sheet.

    Modified script 2:

    In this modification, each request is run in a loop.

    function callNumbers2() {
  const API_KEY = 'xyz';
  const srcSheetName = "Range of Id's";
  const dstSheetName = "user data output";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const ids = srcSheet.getRange("A1:A" + srcSheet.getLastRow()).getDisplayValues();
  const dstValues = ids.map(([id]) => {
    const url = `https://###/wp-json/armember/v1/member_details?arm_api_key=xyz&user_id=${id}19&metakeys=country,checkbox_vgc5x&api_key=${API_KEY}`;
    const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
    return [res.getResponseCode() == 200 ? res.getContentText() : null];
  });
  const dstSheet = ss.getSheetByName(dstSheetName);
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, dstValues.length).setValues(dstValues);
}

    Note:

    • In this sample, it supposes that your showing script for requesting the endpoint works. Please be careful about this.

    References: