I have a code that, based on the IDs in column A, returns the buy and sell results. This behavior only works for cell A2, I would like my code to get the information from the other cells (A3, A4, A5, ...) and bring the information, just like it does with A2.
Thank you very much in advance!
This is my code:
function myFunction() {
var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TRIVIA');
var data = new Date();
data = Utilities.formatDate(data, "GMT-03:00", "yyyy/MM/dd")
f.getRange('F1').activate();
f.getCurrentCell().setValue(data);
var formData = {
'id': f.getRange('A2').getValue(), //A2 é a o ID
'date': f.getRange('F1').getValue(), //F1 é a a data
'server': f.getRange('G1').getValue(), //G1 é o servidor
};
var options = {
'method': 'post',
'payload': formData
};
var result = UrlFetchApp.fetch('https://triviapw.com.br/cotacao/api/item/', options);
var response = JSON.parse(result.getContentText());
var res_code = result.getResponseCode();
if (res_code == 200) {
f.getRange('B2').setValue(response.nome); // nome do item
f.getRange('C2').setValue(response.venda); //menor preço de venda
f.getRange('D2').setValue(response.compra); //maior preço de compra
}
}
CodePudding user response:
In your script, how about the following modification?
Modified script:
function myFunction() {
var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TRIVIA');
var data = new Date();
data = Utilities.formatDate(data, "GMT-03:00", "yyyy/MM/dd");
f.getRange('F1').setValue(data);
var url = 'https://triviapw.com.br/cotacao/api/item/';
var [h, ...v] = f.getDataRange().getValues();
var reqs = v.map(([a]) => ({
url,
method: "post",
payload: {id: a, date: data, server: h[6]},
muteHttpExceptions: true
}));
var res = UrlFetchApp.fetchAll(reqs);
var values = res.map(r => {
var str = r.getContentText();
if (r.getResponseCode() == 200 && str != "Requisição inválida") {
var obj = JSON.parse(str);
return [obj.nome, obj.venda, obj.compra];
}
return Array(3).fill("");
});
f.getRange(2, 2, values.length, values[0].length).setValues(values);
}
- In this modification, at first, the object for requesting is created from the values in the Spreadsheet. And, the object is requested using the fetchAll method. And, the response values are put to the Spreadsheet.