Overview

When writing to Umajin or creating Custom Components, you may want a quick and simple way to store, edit and retrieve data. Especially if you hope to decrease the amount you are coding, using Google Sheets allows you to maintain data in a simple way.

If you create something like a Custom Feed List and don’t want to build a backend in JavaScript to write, edit and store your posts – but you want to use this data as JSON – then Google Drive is a helpful tool.

In this tutorial, we will show you two different methods to convert your spreadsheet data to a JSON format. If you are looking for the easiest way to convert a completed spreadsheet to JSON, look at method 2.

Prerequisites

1. Umajin Editor downloaded and installed
2. Any text editor
3. Completed the registerFeed, registerAction, and http request tutorials.

Using features In Google Sheets to Export JSON

Step 1) Create a new Google Sheet in your Google Drive (drive.google.com) and create a basic table. Ours will hold information regarding students names and the grade that they are in.

Step 2) Freeze the first row, since it contains titles.To do this, highlight the first row, click View → Freeze → 1 row. Now the sheet should resemble this:

Step 3) Go to Tools → Script Editor and a new interface should open in a different tab. Erase all suggested code from the window, and paste the entire JavaScript code found at this this gist.

Step 4) After saving the code, go back to your spreadsheet and reload the page. You should notice that there is a new menu called “Export Json”. Once you click on it, you are presented with three options, shown below.

Step 5) Select one of the three options above. You will then be presented with a JSON sourced from the table in that very spreadsheet.

Step 6) Use this JSON in your Umajin platform when making a Custom Feed or other Data Driven Component.

The problem with this method is that the project does not update with the Google Sheet. You must repeat this process and manually update the JSON to transfer new information to the project.
 

Make Live Feed using web publishing and httpGet

Step 1) Create a new Google Sheet in your Google Drive (drive.google.com) and create a basic table. Ours will hold information regarding students names and the grade that they are in.

Step 2) Publish the Google Sheet to the web. Click File → Publish to the web.

The popup that appears provides you with a few options about what exactly you want to publish. Due to the fact that we are doing a very simple tutorial, we will publish with the default settings.

Once published, the table should appear in a format like this on the webpage:

Step 3) Back on your spreadsheet, copy the page key to your clipboard. The key is what is highlighted below.

Step 4) Create a new JavaScript file. Ours will be called “GSasJson.js”. In this file, you will want to make an httpGet call to https://spreadsheets.google.com/feeds/worksheets/YOUR_KEY_HERE/public/values?alt=json.

Paste your key where it says “YOUR_KEY_HERE”.  This call returns a list of all of the sheets within your workbook.

Step 5) Query using the link created above, but replace the word “worksheets” with the word “lists”.

var feed = '';>
function getSheetData(component){
   feed = component;
   var url = "https://spreadsheets.google.com/feeds/lists/YOUR_KEY_HERE/public/values?alt=json";
   httpGet(url,getSheetData_Success,getSheetData_failure);
}

Step 6) When successful, the Feed List in the Umajin platform is updated. This is done by parsing the JSON that is returned or place it directly into a Custom Feed.

function getSheetData_Success(response){ setCustomFeed(feed,response); } When unsuccessful, the error is printed for the user. function getSheetData_Failure(error){ print("Error: " + error); }