Creating a Google Apps Script to convert data in a spreadsheet to lines of code

Question

I am attempting to build a script using Google Apps Scripts that will take data inputted in a spreadsheet, and convert it into a specified type of code.

Meaning, data would be inputted into a spreadsheet in Google Drive Spreadsheets, then we would run this script to convert the data that was entered into the spreadsheet into a JSON code file that Roku uses with there Direct Publisher service to create a feed for a Roku app. The plan is to allow people, who do not know how to code a JSON file to be able to input data into the spreadsheet (new content for the Roku Streaming App), and then have a script create the code based on the spreadsheet and update the file that is sent to Roku for the app.

I understand how to code in JSON, but I am not sure how to go about creating a script to take the data in a spreadsheet and convert it to the JSON code format. Any help or ideas??

The question is a little bit broad. You should try doing it, then if you get stuck at a particular point ask a more specific question on SO. — Toby 1 Kenobi

Philipp Maurer Philipp Maurer · Accepted Answer · 2017-12-08T10:29:14

You can make a spreadsheet with a key column (column 1) and a value column (column2). Then use:

var sheet = SpreadsheetApp.getActivateSheet(),
    sheetRows = sheet.getSheetValues(1, 1, sheet.getLastRow(), 2),
    jsonObject = sheetRows.reduce(function(roku, row) {
        if (row[0] === '') {
            return;
        }
        roku[row[0]] = row[1];
        return roku;
    }, {});

Drive.createFile(Utilities.newBlob(JSON.stringify(jsonObject, null, '\t')).setName('roku.json');

The code uses Array.reduce to transfere the rows into an object and JSON.stringify to convert it to JSON. It then saves the JSON file in the users drive as roku.json.

A way to give this functionality to your users can be through a custom menu.

Creating a Google Apps Script to convert data in a spreadsheet to lines of code

1 Answers