Map 3D array to cells in Google Sheet - Apps Scirpt

Question

Hey I am trying to place arrays of data into a Google Sheet. I've got a list of dimensions which I am mapping through a function calling an external API to get numerical values for each dimension. I then want to input these into my spreadsheet - however I am unsure how to do this. I would also like to input the specific Date into a column as well. So ideal output would be column A = date, column B = dimensions, column C = numerical value.

function function() {
  var dimensions = ["CN","IN","NZ","US","UK","MY","SG","JP","KR","DE"];
  var counts = dimensions.map(dim => [getValue(dim)]);
}

function getValue(dim) {
  // ive removed the detail for the API call
  var response = UrlFetchApp.fetch(fetchUrl, params);
  var json = JSON.parse(response.getContentText());
  var dataSet = json.results;

  Logger.log(dataSet);

  return dataSet;

}

Answer 1

In your script, for example, how about the following modification?

From:

function function() {
  var dimensions = ["CN","IN","NZ","US","UK","MY","SG","JP","KR","DE"];
  var counts = dimensions.map(dim => [getValue(dim)]);
}

To:

function sample() {
  var dimensions = ["CN","IN","NZ","US","UK","MY","SG","JP","KR","DE"];
  var date = new Date();
  var counts = dimensions.map(dim => [date, dim, getValue(dim)[0].aggregations[0].value]);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
  sheet.getRange(sheet.getLastRow() + 1, 1, counts.length, counts[0].length).setValues(counts);
}

• function cannot be used as the function name.

• From your showing log, I thought that your expected value from the URL might be dataSet[0].dimensions[0].value].

• From ive removed the detail for the API call, I cannot see your whole script. In your script, fetch is used in a loop. For example, when you use fetchAll method, the process cost might be able to be reduced. But, in this case, it is required to also modify getValue function.

• If you want to use this script as the custom function, how about the following script? By this, you can retrieve the result values by putting =sample() to a cell.

    function sample() {
      var dimensions = ["CN","IN","NZ","US","UK","MY","SG","JP","KR","DE"];
      var date = new Date();
      var counts = dimensions.map(dim => [date, dim, getValue(dim)[0].aggregations[0].value]);
      return counts;
    }