Description
Recently, I reported on a workaround for effectively working with Google Sheets tables using Google Apps Script: Ref. This approach addressed limitations in directly retrieving table data and ranges within Apps Script. In this follow-up report, I’m excited to provide a sample script that leverages this workaround to export your valuable Google Sheets tables directly as PDF files. This functionality empowers you to easily share and distribute your data in a clear and universally accessible format.
Sample tables on a Google Spreadsheet
The sample tables are as follows. It supposes that these tables are put into “Sheet1”. This is the same sample of the previous report. Ref
Main class object
This is the main class object of this report. This class object is used for exporting the tables on the Spreadsheet as PDF blobs using the table names.
Please copy and paste the following script to the script editor of the above sample Google Spreadsheet and save the script.
/**
* Class object for converting tables on Spreadsheet to PDF blobs.
* Author: Kanshi Tanaike
* @class
*/
class ConvertTablesToPDFBlobs {
/**
* @param {Object} object Object using this script.
* @param {String} object.spreadsheetId Spreadsheet ID.
*/
constructor(object = {}) {
const { spreadsheetId } = object;
/** @private */
this.spreadsheet = spreadsheetId
? SpreadsheetApp.openById(spreadsheetId)
: SpreadsheetApp.getActiveSpreadsheet();
if (!this.spreadsheet) {
throw new Error(
`No Spreadsheet. Please set the spreadsheet ID like 'new ConvertTablesToPDFBlobs({ spreadsheetId: "###" })'`
);
}
/** @private */
this.endpoint = `https://docs.google.com/spreadsheets/d/${this.spreadsheet.getId()}/export`;
/**
* Please modify this to your actual situation.
* This page will be useful. https://gist.github.com/andrewroberts/c37d45619d5661cab078be2a3f2fd2bb
*
* @private
*/
this.query = {
format: "pdf",
gridlines: false,
vertical_alignment: "MIDDLE",
horizontal_alignment: "CENTER",
};
/** @private */
this.headers = { authorization: "Bearer " + ScriptApp.getOAuthToken() };
}
/**
* ### Description
* Main method.
*
* @return {Array<Blob>} Return PDF blobs.
*/
run(tableNames) {
const a1Notations = this.getA1NotationsFromTables_(tableNames);
return this.getPDFBlobsFromA1Notations(a1Notations);
}
/**
* ### Description
* Get A1Notations from the inputted table names.
*
* @param {Array<String>} tableNames Array including table names on Spreadsheet.
* @return {Array<String>} A1Notations.
* @private
*/
getA1NotationsFromTables_(tableNames) {
const formulas = tableNames.map((tableName) => [
`=LET(tableName, ${tableName},regexText, "'?([^']+)'?!",tableAdress, CELL("address", tableName),checkAddress, REGEXMATCH(CELL("address",tableName),regexText),sheetName, IF(checkAddress,REGEXEXTRACT(tableAdress,"'?([^']+)'?!"),""),rowPos, ROW(tableName) - 1,colPos, COLUMN(tableName),rowLength, ROWS(tableName) + 1,colLength, COLUMNS(tableName),a1Notation1, ADDRESS(rowPos,colPos,4),a1Notation2, ADDRESS(rowPos + rowLength - 1,colPos + colLength - 1,4),IF(sheetName<>"", "'"&sheetName&"'!"&a1Notation1&":"&a1Notation2, a1Notation1&":"&a1Notation2))`,
]);
const tempSheet = this.spreadsheet.insertSheet(
`smaple_${Utilities.getUuid()}`
);
const range = tempSheet.getRange(1, 1, formulas.length);
range.setFormulas(formulas);
SpreadsheetApp.flush();
const a1Notations = range.getDisplayValues().map(([a], i) => ({
tableName: tableNames[i],
a1Notation: a == "#NAME?" ? "No table" : a,
ok: a == "#NAME?" ? false : true,
}));
this.spreadsheet.deleteSheet(tempSheet);
return a1Notations;
}
/**
* ### Description
* Get PDF blobs from A1Notations.
* This method can also be used for just converting A1Notations to PDF blobs.
*
* @param {Array<String>} a1Notations Array including a1Notations.
* @return {Array<Blob>} Blobs.
*/
getPDFBlobsFromA1Notations(a1Notations) {
let requests = [];
if (Array.isArray(a1Notations) && a1Notations.length > 0) {
if (typeof a1Notations[0] == "object") {
requests = a1Notations.reduce((ar, { tableName, a1Notation, ok }) => {
if (ok) {
const range = this.spreadsheet.getRange(a1Notation);
const gid = range.getSheet().getSheetId();
const query = {
...this.query,
gid,
range: a1Notation.split("!").pop(),
};
const url = this.addQueryParameters(this.endpoint, query);
ar.push({ url, headers: this.headers, name: tableName });
}
return ar;
}, []);
} else if (typeof a1Notations[0] == "string") {
requests = a1Notations.map((r) => {
const range = this.spreadsheet.getRange(r);
const gid = range.getSheet().getSheetId();
const query = { ...this.query, gid, range: r };
const url = this.addQueryParameters(this.endpoint, query);
return { url, headers: this.headers, name: r };
});
}
}
if (requests.length > 0) {
const res = UrlFetchApp.fetchAll(requests);
const blobs = res.reduce((ar, r, i) => {
if (r.getResponseCode() == 200) {
const { name } = requests[i];
ar.push(r.getBlob().setName(name));
}
return ar;
}, []);
return blobs;
}
return [];
}
/**
* ### Description
* This method is used for adding the query parameters to the URL.
* Ref: https://tanaikech.github.io/2018/07/12/adding-query-parameters-to-url-using-google-apps-script/
*
* @param {String} url The base URL for adding the query parameters.
* @param {Object} obj JSON object including query parameters.
* @return {String} URL including the query parameters.
*/
addQueryParameters(url, obj) {
if (url === null || obj === null || typeof url != "string") {
throw new Error(
"Please give URL (String) and query parameter (JSON object)."
);
}
return (
(url == "" ? "" : `${url}?`) +
Object.entries(obj)
.flatMap(([k, v]) =>
Array.isArray(v)
? v.map((e) => `${k}=${encodeURIComponent(e)}`)
: `${k}=${encodeURIComponent(v)}`
)
.join("&")
);
}
}
Sample script 1
In this sample, 2 tables shown in the above sample Spreadsheet are exported as PDF files. Please set the table names to tableNames as follows.
function myFunction() {
const tableNames = ["SampleTable1", "SampleTable2"];
const pdfBlobs = new ConvertTablesToPDFBlobs().run(tableNames);
if (pdfBlobs.length == 0) return;
pdfBlobs.forEach((blob) => DriveApp.createFile(blob));
}
When this script is run to the above sample Spreadsheet, the following result is obtained.
SampleTable1
SampleTable2
For example, when you want to export multiple tables as a single PDF file, you can also use the following script. When this script is used, please install PDFApp of a Google Apps Script library. Ref
function myFunction() {
const tableNames = ["SampleTable1", "SampleTable2"];
const pdfBlobs = new ConvertTablesToPDFBlobs().run(tableNames);
if (pdfBlobs.length == 0) return;
PDFApp.mergePDFs(pdfBlobs)
.then((newBlob) => DriveApp.createFile(newBlob).setName("sample.pdf"))
.catch((err) => console.log(err));
}
Sample script 2
When this class is used, not only the tables but also the ranges and the named ranges can be exported as PDF blobs. This sample exports the ranges and the named ranges as PDF blobs.
function myFunction() {
const pdfBlobs = new ConvertTablesToPDFBlobs().getPDFBlobsFromA1Notations([
"'Sheet1'!B2:F6",
"'Sheet1'!B9:H13",
]);
pdfBlobs.forEach((blob) => DriveApp.createFile(blob));
}
When this script is run, the same result with the above sample is obtained.
For example, when the range 'Sheet1'!B2:F6 is set as a named range sampleNamedRange1, you can use the following script.
function myFunction() {
const pdfBlobs = new ConvertTablesToPDFBlobs().getPDFBlobsFromA1Notations([
"sampleNamedRange1",
]);
pdfBlobs.forEach((blob) => DriveApp.createFile(blob));
}