Google Sheets Add-On Tutorial: Exporting Key-Value Column Pairs to JSON

About Google Sheets Add-ons

Add-ons are special scripts that run inside Google Docs, Sheets, and Forms applications. Google sheets Add-ons make it possible to programmatically edit document content, leveraging various Google services in order to perform that task.

The Scenario

A hypothetical multi-language project is in active development, and the developers had enough foresight to keep track of various text resources in a Sheets document. The first column of the spreadsheet represents the unique identifiers (keys) by which the resources are referenced in the program code. Other columns represent the actual contents (values) of the resources, in different spoken languages.

{
“LOCALE”: “EN”,
“btnLaunch”: “Launch application”,
“btnExit”: “Exit application”,
“msgSuccess”: “Launch successfull!”,
“msgFailure”: “Launch failure!”
}

Creating the Google sheets Add-on

From within a new Google Sheets document, navigate to “Tools > Script editor…”. A window running the Apps Script editor should then appear, with a new script file open for editing.

/**
* Apps Script trigger. Runs when the add-on is installed.
*/
function onInstall(e) {
/**
* The document is already open, so after installation is complete
* the ˙onOpen()` trigger must be called manually in order for the
* add-on to execute.
*/
onOpen(e);
}
/**
* Apps Script trigger. Runs when an editable document is opened.
*/
function onOpen(e) {
/**
* Create the Google Sheets add-on menu item in the navigation bar, and have it
* call `showSidebar()` when clicked.
*/
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('Export to JSON', 'showSidebar')
.addToUi();
}
/**
* Creates a new UI element from `Sidebar.html` and shows it.
*/
function showSidebar() {
/**
* Create UI from `Sidebar.html` and set the sidebar title.
*/
var ui = HtmlService.createHtmlOutputFromFile('Sidebar')
.setTitle('Export to JSON');
/**
* Show UI as part of the application sidebar.
*/
SpreadsheetApp.getUi()
.showSidebar(ui);
}
/**
* Stores active range value under provided `key`.
*
* @param {String} key - Property key
*/
function storeRange(key) {
/**
* Get active range from sheet.
*/
var range = SpreadsheetApp.getActiveRange()
/**
* If the range is blank or undefined throw an error.
*/
if (!range || range.isBlank()) {
throw "Select a range!"
}
/**
* Store the active range among document properties,
* in A1 (R1:C1) notation.
*/
PropertiesService.getDocumentProperties()
.setProperty(key, range.getA1Notation())
}
/**
* Retrieves range value assigned to provided `key`.
*
* @param {String} key - Property key
*/
function retrieveRange(key) {
/**
* Retrieve property from document properties.
*/
var property = PropertiesService.getDocumentProperties()
.getProperty(key)
/**
* If property is undefined throw an error.
*/
if (!property) {
throw 'No value assigned to ' + key + '!'
}
/**
* Return range of cells.
*/
return SpreadsheetApp.getActiveSpreadsheet()
.getRange(property)
}
/**
* Exports keys-value column pairs to JSON string.
*/
function exportToJson() {
var properties = PropertiesService.getDocumentProperties()

var keys = SpreadsheetApp.getActiveSheet()
.getRange(properties.getProperty(PROPERTIES.KEYS))
.getValues()

var values = SpreadsheetApp.getActiveSheet()
.getRange(properties.getProperty(PROPERTIES.VALUES))
.getValues()

/**
* Reduce keys and values into an object.
*/
var result = keys.reduce(
function(accumulator, current, index) {
accumulator[current] = values[index][0]
return accumulator
},
{}
)

/**
* Return stringified object.
*/
return JSON.stringify(result)
}
/**
* Constants for property names under which values are stored
* in the document properties object.
*/
var PROPERTIES = {
KEYS: 'KEYS',
VALUES: 'VALUES',
}
function getKeysRange() {
return retrieveRange(PROPERTIES.KEYS);
}
function getValuesRange() {
return retrieveRange(PROPERTIES.VALUES);
}
function setKeysRange() {
storeRange(PROPERTIES.KEYS)
}
function setValuesRange() {
storeRange(PROPERTIES.VALUES)
}

Connecting the UI

The user interface is set up as plain HTML / CSS. Users will interact with the Google Sheets add-on through form buttons, and the add-on will communicate results through text elements.<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<style>textarea { width: 100%; }</style>
</head>
<body>
<div class="sidebar">
<div class="block form-group">
<button id="btn-set-keys">Set Keys</button>
<button id="btn-set-values">Set Values</button>
</div>
<div class="block form-group">
<button class="action" id="btn-export-to-json">Exsport to JSON</button>
</div>
<div class="block form-group">
<label for="ta-result"><b>JSON Result</b></label>
<textarea id="ta-result" rows="15"></textarea>
</div>
<div class="block">
<span id="sp-status"></span>
<span class="error" id="sp-error"></span>
</div>
</div>
<script>
(function app() {
var btnSetKeys = document
.getElementById('btn-set-keys')
.addEventListener('click', setKeys)
var btnSetValues = document
.getElementById('btn-set-values')
.addEventListener('click', setValues)
var btnExporToJson = document
.getElementById('btn-export-to-json')
.addEventListener('click', exportToJson)
var taResult = document
.getElementById('ta-result')
var spStatus = document
.getElementById('sp-status')
var spError = document
.getElementById('sp-error')
// ...
})()
</script>
</body>
</html>
function setKeys() {
google.script.run
.withSuccessHandler(
function(element) {
spStatus.textContent = 'Keys set!'
spError.textContent = ''
}
)
.withFailureHandler(
function(message, element) {
spStatus.textContent = ''
spError.textContent = message
}
)
.withUserObject(this)
.setKeysRange()
}
function setValues() {
google.script.run
.withSuccessHandler(
function(element) {
spStatus.textContent = 'Values set!'
spError.textContent = ''
}
)
.withFailureHandler(
function(message, element) {
spStatus.textContent = ''
spError.textContent = message
}
)
.withUserObject(this)
.setValuesRange()
}

function exportToJson() {
google.script.run
.withSuccessHandler(
function(result, element) {
spStatus.textContent = 'Export successful!'
spError.textContent = ''
taResult.textContent = result
}
)
.withFailureHandler(
function(message, element) {
spStatus.textContent = ''
spError.textContent = message
taResult.textContent = ''
}
)
.withUserObject(this)
.exportToJson()
}

Conclusion

Google application add-ons are simple yet powerful tools for automating tasks related to processing document content. In the presented scenario, the use of a Google Sheets add-on could dramatically speed up the time spent creating localisation files, and also make the entire process less prone to human error.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Factory.hr

Factory.hr

We design and develop native mobile & web applications for startups and successful companies.