Downloading Google Spreadsheet as XLSX and PDF file by Clicking Button

GasTips / spreadsheet
Google Apps Script / spreadsheet / convert / download

Gists

This is a sample script for downloading Google Spreadsheet to the local PC as a XLSX file and a PDF file when a button on the side bar and the dialog is clicked. This is created with Google Apps Script and HTML&Javascript.

Sample script

Please create new Google Spreadsheet and copy and paste the following scripts to the script editor. And please run openSidebar(). By this, the side bar is opened to the Spreadsheet.

Google Apps Script: Code.gs

function openSidebar() {
  const html = HtmlService.createHtmlOutputFromFile("index").setTitle("Sample");
  SpreadsheetApp.getUi().showSidebar(html);
}

function createDataUrl(type) {
  const mimeTypes = { xlsx: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = `https://docs.google.com/spreadsheets/export?exportFormat=${type}&id=${ss.getId()}`;
  const blob = UrlFetchApp.fetch(url, {
    headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
  }).getBlob();
  return {
    data:
      `data:${mimeTypes[type]};base64,` +
      Utilities.base64Encode(blob.getBytes()),
    filename: `${ss.getName()}.${type}`,
  };

  // DriveApp.getFiles() // This is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly".
}

HTML & Javascript: index.html

<input type="button" value="download as XLSX" onclick="download('xlsx')" />
<input type="button" value="download as PDF" onclick="download('pdf')" />
<script>
  function download(type) {
    google.script.run
      .withSuccessHandler(({ data, filename }) => {
        const a = document.createElement("a");
        document.body.appendChild(a);
        a.download = filename;
        a.href = data;
        a.click();
      })
      .createDataUrl(type);
  }
</script>

Note

  • In this sample script, the data is put to the HTML as the data URL and downloaded. This method can be also used for other Google Docs.

References

 Share!