How to Export Key-Value column Pairs to JSON in Google applications

Whether working in-person or as part of a remote team of people, collaboration is an equally important part of the development process. Google’s research suggests that “collaboration has the power to boost innovation and efficiency” in businesses, providing the method of collaboration allows for agility, flexibility, and transparency.
Google’s collaboration tools – such as Docs and Sheets – are commonplace in most development workflows, including our own. They allow for quick and easy sharing of information across teams and individual team members. Plus, they’re easily extensible to suit a wide variety of use-cases.

The aim of this blog post is to demonstrate how to leverage Google application add-ons in order to automate a task. In the provided scenario, a spreadsheet is used to keep track of translated application resources such as error and status messages, or navigation element labels. The end goal is to create a Google Sheets script that prepares JSON-formated output of key-value pairs from resource ID and translation columns, so that localised resources can easily be used within developed software.

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.
Google Sheets Add-ons are written in Apps Script, a JavaScript cloud scripting language developed and maintained by Google. Apps Script’s code editor can be launched from within Docs, Sheets, and Forms applications. This creates a script that is bound to the current document, allowing it to use special methods specific to the application the script is being developed for. For example, Sheets add-ons can call methods that concern the manipulation of spreadsheet cells and ranges.
Apps Script can be used to write both desktop and Android add-ons for Docs, Sheets, and Forms applications.

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.

The project handles localisation by loading an appropriate JSON-formatted file for the desired user interface language. The JSON containers translations which are assigned to unique resource ID keys used within the project sources. An example English resource file could look as follows:

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

Assuming that the localisation files are created from the spreadsheet data and maintained by hand, there is a risk of human error in that some changes or entries in the spreadsheet may accidentally be overlooked.
To remove that risk factor, a  Google Sheets add-on can be created that will take two cell ranges – one for localisation keys, and the other for values – and parse them into valid JSON. The process of creating localisation files thus becomes quick, easy, and more reliable.

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.
The Google Sheets add-on needs to listen for certain application events in order to begin execution. Apps Script provides a number of simple triggers for that purpose. Specifically, onInstall(e) and onOpen(e) are key to the add-on development process: the former runs when the add-on is installed, and the latter when the document is opened 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();
}

The add-on requires a simple user interface through which the user can select cell ranges for resource keys and translation values, and ultimately create the desired JSON output. User interface elements are written in HTML / CSS and interact with the add-on script via JavaScript.
The user interface can be displayed in a modal dialog, modeless dialog, or sidebar. The Google Sheets add-on will use a sidebar. The HTML layout is created within the Apps Script editor by selecting “File > New > Html file”, prompting for a file name (e.g. “Sidebar”). It is then parsed and handled by the HtmlService API which is made to create and serve HTML files. UI layout creation is covered in more detail in the next chapter.

/**
 * 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);
}

At this point the Google Sheets add-on can be tested, even though it does not perform any actions. To test the add-on, it is necessary to refresh the document within which the Apps Script editor was launched. The editor window will close, and the add-on will be installed and available from the “Add-ons” navigation menu. Further edits to any add-on source files only require for the add-on to be restarted before changes take effect.
Note that executing the add-on will request permissions for editing documents on the user’s Google Drive. This is expected – it is intended for add-ons to manipulate document content.
Using the Sheets API
The Apps Script platform exposes a number of different APIs to facilitate development. Two such APIs have already been used: SpreadsheetApp, for interacting with the Sheets application; and HtmlService, for working with HTML layout files. Another useful API is PropertiesService, which makes it possible to store and persist data in the context of document, user, or script properties.
Considering the requirements put forth by the scenario described previously, the Google Sheets add-on should make it possible for the user to store selected cell ranges as keys and values in the localisation JSON.

/**
 * 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)
}

Using the selections to create a JSON string is straightforward. Key and value ranges are read from document properties in A1 notation. The ranges are then fetched from the active sheet. Following that, their values are reduced into an object. The object is finally stringified and returned as the function result.

/**
 * 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)
}

To facilitate UI interaction, a couple of helper actions are introduced that wrap around the existing functions. These concern the swift retrieval and storage of selected cell ranges.

/**
 * 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">Export 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>

The UI connects with the add-on script through a special client-side API in the google.script namespace. Note that functions defined as part of the add-on script are called asynchronously, and their results passed to failure and success handlers for further processing. If an executed function throws an error, that error will be passed on to the failure handler. Alternatively, any value returned by the executed function will be passed on to the success handler. A special user-defined object can also be set for use with handlers, in case a certain context of execution needs to be preserved.

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()
}

With the above changes made, the add-on is complete! Launching the add-on from within the Sheets document presents a sidebar with action buttons and a text area for the export result. By selecting a cell range or column and pressing “Set Keys”, the cell range for resource IDs is set. By selecting a cell range or column and pressing “Set Values”, the cell range for translated resources is set. These are kept as part of the document properties and persist throughout the document, meaning that it’s not necessary to re-set the keys range if the values range has been changed.

The “Export to JSON” button takes the ranges stored in document properties, creates a resource localisation object in accordance with design, and displays the stringified object in the result text area. This can then be copied to a file and used within the project.

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.
But this only scratches the surface of add-on capabilities. Hopefully, this blog post will encourage the creation of new add-ons for Docs, Sheets, and Forms applications that will transform Google’s collaboration tools in exciting and creative ways.
Happy coding!