Think Labs is an ongoing effort by Seven2 to provide research and educational opportunities in the web development and mobile field. To see what we’ve been cookin’ up, check out our blog postings.

Created by
Seven2 Login

Team Think Labs | JSON with Google Apps Script, Some Backend GAS
single,single-post,postid-4860,single-format-standard,ajax_fade,page_not_loaded,,,wpb-js-composer js-comp-ver-4.2.3,vc_responsive

JSON with Google Apps Script, Some Backend GAS

Lots of web projects, at some point, contain a spreadsheet that eventually turns into a list of items on a web page via JSON. A lot of time and effort is spent transferring spreadsheet data to databases or other friendly formats. Thanks to Google Apps Script, it’s possible to host a JSON file on their servers that’s driven by a spreadsheet. Here’s an example of outputting a spreadsheet to JSON:

Full example code on Gist


Create a Script Project

To output your spreadsheet to a JSON file first create a new Script project:


Screen Shot 2014-06-03 at 11.28.47 AM

Setup doGet function

You will want to create a doGet(request) function in a .gs file. This is part of the UI-Service component, it’s the entry point for code accessed via a URL, the URL parameters are passed in.




Project Properties

Constants can be stored in your project in File -> Project Properties. A nice place for the Spreadsheet ID:

Screen Shot 2014-06-03 at 11.54.40 AM



 var scriptProperties = PropertiesService.getScriptProperties();

  //Logger.log( HtmlService.createHtmlOutputFromFile(scriptProperties.));


Open and Read Spreadsheet

You have access to the spreadsheet via the SpreadsheetApp Class. Method openByID will open our spreadsheet. The ID of your spreadsheet can be found in the spreadsheet’s URL https://docs.google.com/spreadsheets/d/<spreadsheet ID>/edit#gid=0


Publish App

Once you are ready to publish your app Select Publish -> Deploy as Web Page

Screen Shot 2014-06-03 at 11.30.25 AM


Quotas and Limitations

Some good things to be aware of, there are some limitations with Google Apps Scripts:

Screen Shot 2014-06-03 at 11.34.13 AM