Downloading Active Sheet in Google Spreadsheet as CSV and PDF file by Clicking Button

submit.md

Downloading Active Sheet in Google Spreadsheet as CSV and PDF file by Clicking Button

This is a sample script for downloading the active sheet in Google Spreadsheet to the local PC as a CSV 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. In this post, the script of the previous post was modified.

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 = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let url = null;
  if (type == "csv") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()}`;
  } else if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${sheet.getSheetId()}`;
  }
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };

  // 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 CSV" onclick="download('csv')" />
<input type="button" value="download as PDF" onclick="download('pdf')" />
<script>
  function download(type) {
    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();
        }
      })
      .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

• Class google.script.run
• base64Encode(data)
• Related post in my blog
  • Downloading Google Spreadsheet as XLSX and PDF file by Clicking Button