Skip to content

Instantly share code, notes, and snippets.

@adborden
Created August 28, 2016 22:16
Show Gist options
  • Save adborden/a3a24c4b1ed3a7a17f4beab8595ea450 to your computer and use it in GitHub Desktop.
Save adborden/a3a24c4b1ed3a7a17f4beab8595ea450 to your computer and use it in GitHub Desktop.
Turn google spreadsheet into a JSON API
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, '');
}
@sahujaunpuri
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment