Skip to content

Instantly share code, notes, and snippets.

@rascode
Last active July 18, 2023 10:24
Show Gist options
  • Save rascode/5779341 to your computer and use it in GitHub Desktop.
Save rascode/5779341 to your computer and use it in GitHub Desktop.
Use this script to build a javascript object array from a Google Spreadsheet.
//display custom menu
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Export to JSON')
.addItem('Get Sheet Header', 'getSheetHeader')
.addItem('Get Sheet Data', 'getSheetData')
.addItem('Download Spreadsheet as JSON', 'doGet')
.addToUi();
}
//Declare gloabl variables
var sheet = SpreadsheetApp.getActiveSheet();
var allSheetData = sheet.getDataRange().getValues(); //get all spreadsheet data
var filename = SpreadsheetApp.getActive().getName();
var headerRow = allSheetData[0];
//Get the Header Row of current Sheet
function getSheetHeader() {
return sheetHeader; //return the header row of the spreadsheet
}
//Get data of current sheet
function getSheetData(){
var data = []; //create an empty array to hold spreadsheet data
//loop through each row in the spreadsheet
for (var currentRow = 1; currentRow < allSheetData.length; currentRow++) {
//Create an object to hold the data from the current row
var obj = {};
//loop through each column in the current row of the spreadshset
for(var rowColumn=0;rowColumn<headerRow.length;rowColumn++){
obj[headerRow[rowColumn]]=allSheetData[currentRow][rowColumn];
}
data.push(obj);
}
return data;
}
function doGet() {
return ContentService.createTextOutput(JSON.stringify(getSheetData())).setMimeType(ContentService.MimeType.JSON);}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment