CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets
user7962929
user7962929

Reputation:

JSON data not parsed correctly from Google Sheets

I'm trying to convert Google Sheets Data to JSON format using a script.

This is a test google sheet: https://docs.google.com/spreadsheets/d/1zEts1AaAseO4MlLf5OA--Ylp-bH9aXe-1pacjstv4GI/edit#gid=0

But the JSON Format looks like this: JSON Format of the Test Sheet

which is not correct at all! Script code is given below with correct credentials of the test sheet:

function doGet(e) {

  var sheetName = "TestSheet";
  var sheetId   = "1zEts1AaAseO4MlLf5OA--Ylp-bH9aXe-1pacjstv4GI";

  var book = SpreadsheetApp.openById(sheetId);
  var sheet = book.getSheetByName(sheetName);

  var json = convertSheet2JsonText(sheet);

  return ContentService
      .createTextOutput(JSON.stringify(json))
      .setMimeType(ContentService.MimeType.JSON);
}

function convertSheet2JsonText(sheet) {

  var sheetName = "TestSheet";
  var sheetId   = "1zEts1AaAseO4MlLf5OA--Ylp-bH9aXe-1pacjstv4GI";

  var book = SpreadsheetApp.openById(sheetId);
  var sheet = book.getSheetByName(sheetName);
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(2, 2, 2, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, 
  sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
}

// create json
  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
   jsonArray.push(json);
  }
  return jsonArray;
 }

I am guessing the logic inside the convertSheet2JsonText function is wrong which is causing the problem. Your help will be much appreciated. Thank you.

The correct JSON format of this test sheet should look like this: Right Format of JSON

Upvotes: 0

Views: 645

Answers (1)

Tanaike
Tanaike

Reputation: 201613

You want to retrieve the following object from the shared spreadsheet using your script.

[
  {
    "firstName": "john",
    "lastName": "doe",
    "age": 12,
    "place": "boston"
  },
  {
    "firstName": "nahid",
    "lastName": "patwary",
    "age": 21,
    "place": "sylhet"
  },
  {
    "firstName": "kishor",
    "lastName": "pasha",
    "age": 15,
    "place": "california"
  }
]

If my understanding is correct, how about this modification? I think that the range for retrieving titleColumns might be the reason of your issue. So how about modifying as follows?

From:

var firstRange = sheet.getRange(2, 2, 2, sheet.getLastColumn());

To:

var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());

If I misunderstand your question, please tell me. I would like to modify it.

Upvotes: 1

Related Questions