GAS - Download Sheet as JSON

INTRO

OK, you have your google sheet with headers, columns, and rows, you need to download the data to your app, but in json (dictionary format)

Create a google apps script web app to do all the heavy lifting for you!

SETUP

• Create a spreadsheet and setup your data with a header row, then more rows with data (no other layout, just columns and rows)

• In your spreadsheet, go to Tools, Extensions, AppScript, and create your web app - see script below

• Create a new project in AppInventor and setup the blocks to call the web app (you will need the script url, the spreadsheet ID and the sheet name) - see blocks below

SHEET

some example data in a sheet named Sheet1

SCRIPT

Copy and paste this script into your gas editor, removing any other text there. Deploy your script as a web app and get the script url. If you don't know how to do this, follow the links in this guide: Everything you wanted to know about Google Apps Script

function doGet(e) {

 //ref: https://stackoverflow.com/a/68365891/2030549

 const ss = SpreadsheetApp.openById(e.parameter.SheetID);

 const sh = ss.getSheetByName(e.parameter.SheetName);

 const rg = sh.getDataRange().getValues();

 const hr = sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];

 var json= [];

 for (var i=1;i<rg.length;i++) {

 var obj = {};

       var row = rg[i];

       for (var j=0;j<hr.length;j++) {

           obj[hr[j]] = row[j];

       }

       json.push(obj);

 }

 return ContentService.createTextOutput(JSON.stringify(json));

}

BLOCKS

The responseContent will not actually provide a dictionary as such, but a list containing a dictionary of each row in the spreadsheet

EXAMPLE OUTPUT

[

{"id":"01K1173680SMK4P2MJ8DMG85K0","first_name":"Allayne","last_name":"Lusgdin","email":"alusgdin0@youtu.be","gender":"Male","ip_address":"132.130.180.127"},{"id":"01K1173681DTW550PE0W14NY6Z","first_name":"Annabell","last_name":"Lindgren","email":"alindgren1@howstuffworks.com","gender":"Female","ip_address":"183.128.181.36"},

{"id":"01K11736824MPBEJ5BG5VJ5XQY","first_name":"Joel","last_name":"Moffat","email":"jmoffat2@rambler.ru","gender":"Male","ip_address":"226.46.212.104"},{"id":"01K1173682S21YEF2MWKDD1ZHK","first_name":"Valentijn","last_name":"Falla","email":"vfalla3@va.gov","gender":"Male","ip_address":"54.98.140.232"},{"id":"01K1173683SBVEJ16GY08G4G16","first_name":"Yvon","last_name":"Mariet","email":"ymariet4@gov.uk","gender":"Male","ip_address":"66.31.145.38"},{"id":"01K1173684WBRSY7T77DCZJTTJ","first_name":"Gayelord","last_name":"Isard","email":"gisard5@gravatar.com","gender":"Male","ip_address":"244.172.99.45"},{"id":"01K1173684Q9GECSQ9RX2K5CRT","first_name":"Gamaliel","last_name":"Crighton","email":"gcrighton6@tumblr.com","gender":"Male","ip_address":"212.147.145.27"},{"id":"01K11736859M5600QKVFMN5A5T","first_name":"Janean","last_name":"Silly","email":"jsilly7@blogspot.com","gender":"Female","ip_address":"75.33.66.216"},{"id":"01K11736868BHW9V5B8WC1Z4XT","first_name":"Amos","last_name":"Satchell","email":"asatchell8@noaa.gov","gender":"Male","ip_address":"175.238.149.226"},{"id":"01K1173686EPW71BD7BJR7MDGV","first_name":"Sukey","last_name":"Dey","email":"sdey9@360.cn","gender":"Female","ip_address":"244.247.54.71"},{"id":"01K11736875YHF05B4F5Y6ZF2Q","first_name":"Petey","last_name":"Kerbler","email":"pkerblera@cdc.gov","gender":"Male","ip_address":"155.231.184.143"},{"id":"01K1173688K76EHMQ3RW3D3QC6","first_name":"Jameson","last_name":"Beckham","email":"jbeckhamb@cornell.edu","gender":"Male","ip_address":"167.106.42.147"},{"id":"01K1173688PZRYG1CDXK66PQCK","first_name":"Delmer","last_name":"Throughton","email":"dthroughtonc@jigsy.com","gender":"Male","ip_address":"68.180.202.118"},

...

]

RESOURCES

You can test the script in your computer browser by entering the script url with the parameters required. For example:

https://script.google.com/macros/s/AKfycbwiUmZ_edqDUgP...ylCq_6iNcmVWaO/exec?SheetID=1hjlRjar82A....X8291Vk&SheetName=Sheet1