So, I'm trying to build an API using my Google Sheet and I've run into a problem.
Here's my code
function doGet() {
let doc = SpreadsheetApp.getActiveSpreadsheet();
let sheet = doc.getSheetByName('portfolios');
let values = sheet.getDataRange().getValues();
let output = [];
for (let i = 2; i < values.length; i++) {
let row = [];
row['name'] = values[i][0];
row['committee'] = values[i][1];
row['post'] = values[i][2];
//console.log(row)
output.push(row);
}
console.log(output)
return ContentService.createTextOutput(JSON.stringify(output)).setMimeType(ContentService.MimeType.JSON);
}
When I run the script, I get the desired output in the console but when I deploy the API (I've set "Who has access" to Everyone) and send a GET request, I receive an empty JSON object.
[[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]]
What am I doing wrong?
On behalf of OP, Himanshu Sardana
I was able to fix the error, turns out it was due to me using the JSON.stringify function incorrectly. I was able to fix it by changing:
let row = [];
to
let row = {};
Edited code:
function doGet() {
let doc = SpreadsheetApp.getActiveSpreadsheet();
let sheet = doc.getSheetByName('portfolios');
let values = sheet.getDataRange().getValues();
let output = [];
for (let i = 2; i < values.length; i++) {
let row = {}; // Edit in question
row['name'] = values[i][0];
row['committee'] = values[i][1];
row['post'] = values[i][2];
//console.log(row)
output.push(row);
}
console.log(output)
return ContentService.createTextOutput(JSON.stringify(output)).setMimeType(ContentService.MimeType.JSON);
}