function convertDatatoJson(sheet) {
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var sheetData = sheet.getRange(1,1,lastRow,lastColumn).getValues();
var title = sheetData.shift();
return sheetData.map(arr => {
var obj = {}; // create json
arr.forEach((item,i) => obj[title[i]] = item);
return obj;
})
}
var URL_gSHEET = 'https://docs.google.com/spreadsheets/d/1vRUrmGu_jMzNvHGWza--8eZgyoZg7N6G0PnnnwwcLGA/edit#gid=0';
var SHEET_NAME = 'Sheet1';
function doGet(e) {
var url = URL_gSHEET;
var sheetName = SHEET_NAME;
var book = SpreadsheetApp.openByUrl(url);
var sheet = book.getSheetByName(sheetName);
var json = convertDatatoJson(sheet);
return ContentService.createTextOutput(JSON.stringify(json))
.setMimeType(ContentService.MimeType.JSON);
}//===========================================================================================================
function convertSheet2Json(sheet) {
// title
var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var firstRowValues = firstRange.getValues();
var titleColumns = firstRowValues[0];
// 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;
}
var URL_gSHEET = 'https://docs.google.com/spreadsheets/d/1vRUrmGu_jMzNvHGWza--8eZgyoZg7N6G0PnnnwwcLGA/edit#gid=0';
var SHEET_NAME = 'Sheet1';
function doGet(e) {
var url = URL_gSHEET;
var sheetName = SHEET_NAME;
var book = SpreadsheetApp.openByUrl(url);
var sheet = book.getSheetByName(sheetName);
var json = convertSheet2Json(sheet);
console.log(ContentService.createTextOutput(JSON.stringify(json))
.setMimeType(ContentService.MimeType.JSON))
return ContentService.createTextOutput(JSON.stringify(json))
.setMimeType(ContentService.MimeType.JSON);
}