Download Google Sheet data as JSON from browser through custom menu

Question

Is there a way to download JSON directly from a Google Sheet, like this:

I want to create a custom menu with an App Script. There would be a button that would create the JSON object from the sheet data and when it is finished it would download it immediately as a file. So there is no GDrive or other apps involved, just a Google Sheet and an App Script.

Is this somehow possible to do? I could not find anything like this.

Answer 1

I believe your goal as follow.

• You want to run the script from the custom menu.
• You want to download the active sheet as a file including a JSON data.
• The sample JSON data is as follows.
  • [{"a1": "a2", "b1": "b2"},{"a1": "a3", "b1": "b3"},,,]

In this case, in order to download a file, it is required to use Javascript. For this, in this answer, a dialog is used. So the flow of this sample script is as follows.

1. Run the script from the custom menu.
2. A dialog is opened.
3. Javascript is run on the dialog, and the file is downloaded.
4. The dialog is closed.

When above flow is reflected to a script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor and run onOpen or reopen the Spreadsheet. By this, the custom menu is created.

Google Apps Script side: Code.gs

Please copy and paste the following script to the script editor as the Google Apps Script.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Menu').addItem('Export to JSON', 'download').addToUi();
}

function download() {
  const html = HtmlService.createHtmlOutputFromFile("index");
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
}

function downloadFile() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const [header, ...values] = sheet.getDataRange().getValues();
  const obj = values.map(r => r.reduce((o, c, j) => Object.assign(o, {[header[j]]: c}), {}));
  const filename = `${sheet.getSheetName()}.txt`;
  const blob = Utilities.newBlob(JSON.stringify(obj), MimeType.PLAIN_TEXT, filename);
  return {data: `data:${MimeType.PLAIN_TEXT};base64,${Utilities.base64Encode(blob.getBytes())}`, filename: filename};
}

HTML&Javascript side: index.html

Please copy and paste the following script to the script editor as the HTML.

<script>
google.script.run
  .withSuccessHandler(({ data, filename }) => {
    if (data && filename) {
      const a = document.createElement("a");
      document.body.appendChild(a);
      a.download = filename;
      a.href = data;
      a.click();
    }
    google.script.host.close();
  })
.downloadFile();
</script>

References:

• Custom Menus in Google Workspace
• Dialogs and Sidebars in Google Workspace Documents