Last active
July 18, 2023 10:24
-
-
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.
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
//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