AppScript API returning empty JSON object

Himanshu Sardana 0
Viewed 49

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?

google-sheets google-apps-script json javascript
1 Answers

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);
}

Ricardo Jose Velasquez Cruz0
Related