Created
August 28, 2016 22:16
-
-
Save adborden/a3a24c4b1ed3a7a17f4beab8595ea450 to your computer and use it in GitHub Desktop.
Turn google spreadsheet into a JSON API
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function doGet(request) | |
{ | |
var spreadsheetId = 'YOUR SPREADSHEET ID HERE'; | |
var sheetId = request.parameters.sheet || 0; | |
// Get data from the sheet | |
var sheet = getSheet(spreadsheetId, sheetId); | |
var dataArray = getData(sheet); | |
// Normalize the headers | |
var headers = dataArray.shift().map(function (header) { return normalizeName(header); }) | |
// Map rows to JSON | |
var response = dataArray.map(function (row) { | |
return row.reduce(function (memo, value, idx) { | |
memo[headers[idx]] = value; | |
return memo; | |
}, {}); | |
}); | |
return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON) | |
} | |
function getSheet(spreadsheetId, sheetId) | |
{ | |
var ss = SpreadsheetApp.openById(spreadsheetId); | |
return ss.getSheets()[sheetId]; | |
} | |
function getData(sheet) | |
{ | |
var sheet_range = sheet.getDataRange(); | |
return sheet_range.getValues(); | |
} | |
// Lowercase, spaces to _, strip the rest | |
function normalizeName (name) { | |
return name.toLowerCase().replace(/ /g, '_').replace(/[^a-z0-9_-]/g, ''); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
By this, we can only get data of 1 sheet.
If we need to get more than 1 sheet data, then what needs to be changed