-
-
Save sahujaunpuri/d1162f6ed81585752131b4cc2f6ab9fe 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