Updated on January 25, 2024
Overview:
This is a report for challenging exporting the selected cells on Spreadsheet as an image using Google Apps Script and Javascript.
Description:
This report is based on this question by Max Makhrov. When I saw this question, I remembered that there are many questions for asking this in Stackoverflow. And, I thought that when this is achieved, it will be useful for the owner of this question and a lot of users. So, I have discussed this in his question.
When we discussed this, it was found the following issues.
- When the border is used for the cells, it seems that the row height + the border size is different from the exported result. Ref
- Pixel size might not be changed linearly with the value of row height and border size. Ref
- When I tested the cell size including the borders, I thought that the tendency of change of size might be different between height and width. Ref
- When the row height is the default (21 from getRowHeight) and the text font size in the cell is increased, the value retrieved by getRowHeight is not changed from 21. Ref
There is also issue with wrapping text inside a cell which on my experience also causes errors in a pixel size of cell.Ref- From your question, when the selected cell range is large, the number of pages is more than 2. In this case, all pages cannot be correctly merged as an image.
From the above situation, I’m worried that obtaining the correct size of the selected cells might be difficult. So, I proposed to process this as image processing. Ref I thought that when this process is run with the image processing, the above issues might be able to be avoided.
But, unfortunately, in order to process this as image processing, there is no built-in method in Google Apps Script. But, fortunately, in this question, Javascript can be used in a dialog. So, I created a Javascript library for achieving this process as the image processing. Ref
When this Javascript library is used, the sample demonstration can be seen at the above movie.
Usage:
1. Prepare a Spreadsheet.
Please create a new Spreadsheet and put several values to the cells.
2. Sample script.
Please copy and paste the following script to the script editor of Spreadsheet.
Google Apps Script side: Code.gs
function getActiveRange_(ss, borderColor) {
const space = 5;
const sheet = ss.getActiveSheet();
const range = sheet.getActiveRange();
const obj = {
startRow: range.getRow(),
startCol: range.getColumn(),
endRow: range.getLastRow(),
endCol: range.getLastColumn(),
};
const temp = sheet.copyTo(ss);
const r = temp.getDataRange();
r.copyTo(r, { contentsOnly: true });
temp
.insertRowAfter(obj.endRow)
.insertRowBefore(obj.startRow)
.insertColumnAfter(obj.endCol)
.insertColumnBefore(obj.startCol);
obj.startRow += 1;
obj.endRow += 1;
obj.startCol += 1;
obj.endCol += 1;
temp
.setRowHeight(obj.startRow - 1, space)
.setColumnWidth(obj.startCol - 1, space)
.setRowHeight(obj.endRow + 1, space)
.setColumnWidth(obj.endCol + 1, space);
const maxRow = temp.getMaxRows();
const maxCol = temp.getMaxColumns();
if (obj.startRow + 1 < maxRow) {
temp.deleteRows(obj.endRow + 2, maxRow - (obj.endRow + 1));
}
if (obj.startCol + 1 < maxCol) {
temp.deleteColumns(obj.endCol + 2, maxCol - (obj.endCol + 1));
}
if (obj.startRow - 1 > 1) {
temp.deleteRows(1, obj.startRow - 2);
}
if (obj.startCol - 1 > 1) {
temp.deleteColumns(1, obj.startCol - 2);
}
const mRow = temp.getMaxRows();
const mCol = temp.getMaxColumns();
const clearRanges = [
[1, 1, mRow],
[1, obj.endCol, mRow],
[1, 1, 1, mCol],
[obj.endRow, 1, 1, mCol],
];
temp
.getRangeList(clearRanges.map((r) => temp.getRange(...r).getA1Notation()))
.clear();
temp
.getRange(1, 1, 1, mCol)
.setBorder(
true,
null,
null,
null,
null,
null,
borderColor,
SpreadsheetApp.BorderStyle.SOLID
);
temp
.getRange(mRow, 1, 1, mCol)
.setBorder(
null,
null,
true,
null,
null,
null,
borderColor,
SpreadsheetApp.BorderStyle.SOLID
);
SpreadsheetApp.flush();
return temp;
}
function getPDF_(ss, temp) {
const url =
ss.getUrl().replace(/\/edit.*$/, "") +
"/export?exportFormat=pdf&format=pdf" +
// + '&size=20x20' // If you want to increase the size of one page, please use this. But, when the page size is increased, the process time becomes long. Please be careful about this.
"&scale=2" +
"&top_margin=0.05" +
"&bottom_margin=0" +
"&left_margin=0.05" +
"&right_margin=0" +
"&sheetnames=false" +
"&printtitle=false" +
"&pagenum=UNDEFINED" +
"horizontal_alignment=LEFT" +
"&gridlines=false" +
"&fmcmd=12" +
"&fzr=FALSE" +
"&gid=" +
temp.getSheetId();
const res = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
});
return (
"data:application/pdf;base64," + Utilities.base64Encode(res.getContent())
);
}
// Please run this function.
function main() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const temp = getActiveRange_(ss, "#000000");
const base64 = getPDF_(ss, temp);
let htmltext = HtmlService.createTemplateFromFile("index")
.evaluate()
.getContent();
htmltext = htmltext.replace(/IMPORT_PDF_URL/m, base64);
const html = HtmlService.createTemplate(htmltext)
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.NATIVE);
SpreadsheetApp.getUi().showModalDialog(html, "sample");
ss.deleteSheet(temp);
}
function saveFile(data) {
const blob = Utilities.newBlob(
Utilities.base64Decode(data),
MimeType.PNG,
"sample.png"
);
return DriveApp.createFile(blob).getId();
}
HTML & Javascript side: index.gs
Here, I used a Javascript library of CropImageByBorder_js for processing this as the image processing.
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdf.js/3.11.174/pdf.min.js"></script>
<script src="https://cdn.jsdelivr.net/gh/tanaikech/CropImageByBorder_js@latest/cropImageByBorder_js.min.js"></script>
<canvas id="canvas"></canvas>
<script>
var pdfjsLib = window["pdfjs-dist/build/pdf"];
pdfjsLib.GlobalWorkerOptions.workerSrc = 'https://cdnjs.cloudflare.com/ajax/libs/pdf.js/3.11.174/pdf.worker.min.js';
const base64 = "IMPORT_PDF_URL"; //Loaading the PDF from URL
const cvs = document.getElementById("canvas");
pdfjsLib.getDocument(base64).promise.then((pdf) => {
const { numPages } = pdf;
if (numPages > 1) {
throw new Error(
"Sorry. In the current stage, this sample script can be used for one page of PDF data. So, please change the selected range to smaller."
);
}
pdf.getPage(1).then((page) => {
const viewport = page.getViewport({ scale: 2 });
cvs.height = viewport.height;
cvs.width = viewport.width;
const ctx = cvs.getContext("2d");
const renderContext = { canvasContext: ctx, viewport: viewport };
page.render(renderContext).promise.then(async function () {
const obj = { borderColor: "#000000", base64Data: cvs.toDataURL() };
const base64 = await CropImageByBorder.getInnerImage(obj).catch((err) =>
console.log(err)
);
const img = new Image();
img.src = base64;
img.onload = function () {
cvs.width = img.naturalWidth;
cvs.height = img.naturalHeight;
ctx.drawImage(img, 0, 0);
};
google.script.run
.withSuccessHandler((id) => console.log(id))
.saveFile(base64.split(",").pop());
});
});
});
</script>
3. Testing
When you test this script, please select the cells and run main(). By this, the selected cells are exported as an image (PNG) to the root folder as follows. In this case, you can see the above demonstration.
4. Flow.
In this sample script, the following flow is used.
- Manually select the cells, and run the script of
main(). - At the script, the selected cells enclosed by the single row and column are created as a temporal sheet.
- Export the temporal sheet as a PDF data as base64. Here, the PDF data is sent to Javascript side.
- Convert 1st page of PDF data to an image using PDF.js.
- Cropping the selected cells using CropImageByBorder_js, and return the result image to Google Apps Script side.
- Save the image as a file to Google Drive.
LIMITATION:
-
In this sample script, it supposes that the selected range is put on one PDF page. So, when you select a large range, when the number of PDF pages is more than 2, unfortunately, this script cannot be used. So, please be careful about this.
-
And also, in this case, Javascript is used on a dialog. So, when you use this sample script, it is required to open the Spreadsheet and select the cells and run the script.
References:
- PDF.js
- CropImageByBorder_js
- I created this Javascript library for using this sample script.
- I proposed this sample script to this question of Stackoverflow.