0
215
VisualizaçõesMap 3D array to cells in Google Sheet - Apps Scirpt
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;
}
1 Respostas
Responde à pergunta0
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);
}
functioncannot 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,fetchis used in a loop. For example, when you usefetchAllmethod, the process cost might be able to be reduced. But, in this case, it is required to also modifygetValuefunction.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; }