save data from API response to using it app script-CodePudding

I'm using app script to get data from API to put it to google sheet. I'm using one API Endpoint to get many information, Example of response: {"name": "john", "age": "24", "gender": "male", "price": "10",} .

This is my code to get every value :

function getName() {

       var url = "*****************";
       var apiRequest = UrlFetchApp.fetch(url);
       var jsonResponce = JSON.parse(apiRequest);
       var name= jsonResponce.name;
       return name;
  }

I add this function in cell like that getName() and return me the value.

So I do this way in the 4 value : name & age & gender & price, by one API and multi function like above getName() & getAge() & getGender() & getPrice() . every function put in another cell to get value.

this way called API 4 time in same time. I want to call this API response one time and return data and stored data : then I need to give the every value from this data, without calling API 4 time. calling one time in the first.

CodePudding user response：

Call it once and save it to a global variable apiResponse. Use a function to return the desired values from the apiResponse:

var url = "*****************";
var apiRequest = UrlFetchApp.fetch(url);
var apiResponse = JSON.parse(apiRequest);

function getValue(key) {
  return apiResponse[key]
}

Now in your google sheet just add =getValue("name") in the cell to get the name.

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the values of name, age, gender, and price by one API call and put them to the Spreadsheet.
You want to achieve this using a custom function.

In this case, how about the following sample script? From your question, I understood that the returned value is {"name": "john", "age": "24", "gender": "male", "price": "10",}. In this case, the following script returns the values you want.

function sample() {
  var jsonResponce = {"name": "john", "age": "24", "gender": "male", "price": "10"};
  const keys = ["name", "age", "gender", "price"]; // You can set the order of output values here.
  return [keys.map(k => jsonResponce[k])];
}

When the above script is reflected in your script, it becomes as follows.

Sample script:

function sample() {
  var url = "*****************";
  var apiRequest = UrlFetchApp.fetch(url);
  var jsonResponce = JSON.parse(apiRequest.getContentText());
  const keys = ["name", "age", "gender", "price"]; // You can set the order of output values here.
  return [keys.map(k => jsonResponce[k])];
}

When you put =sample() to a cell, each value is put to a row. When you want to put the values to a column, please modify return [keys.map(k => jsonResponce[k])]; to return keys.map(k => jsonResponce[k]).

Note:

When you don't use V8 runtime, please modify as follows.

  var keys = ["name", "age", "gender", "price"]; // You can set the order of output values here.
  return [keys.map(function(k) {return jsonResponce[k]})];

Sample script:

Note:

References: