I am trying to make multiple images from the sheet range and save that image in google drive and paste its link to the sheet using google app script. I have searched many questions here but no luck yet.
So the process is like, I have 10 stores date. I am using app script to filter out and paste the each stores data into another sheet and than create an image for that data range. This make in total 10 images.
app script and java script that I am using right is for creating only 1 image also there is a limitation i have to keep the spread sheet open for this to work. I want to trigger this function.
Sample Image
function EmailAsExcelAttachment() {
var ss = SpreadsheetApp.getActive();
var dump = ss.getSheetByName("Dump");
var s = ss.getSheetByName('Store_Summary');
var ed = ss.getSheetByName('Emails');
const mailmerge = ss.getSheetByName('Mail Merge');
var n=3//ed.getLastRow();
mailmerge.getRange("A2:F").clearContent();
s.getRange('B5:T').clearContent();
for (var i = 2; i < n+1 ; i++ )
{
var merchant_id = ed.getRange(i,2).getValue();
var range = dump.getRange(1, 1, dump.getLastRow(), dump.getLastColumn());
var active_stores = ed.getRange(i,7).getValue();
var filter = range.getFilter() || range.createFilter();
var foo_index = 1;
var filterValue = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(merchant_id).build()
filter.setColumnFilterCriteria(foo_index, filterValue)
var sourceRange = dump.getRange('C:U');
sourceRange.copyTo(s.getRange('B5'),{contentsOnly:true});
var report_stores = ed.getRange("L2").getValue();
/*Logger.log(i);
Logger.log(report_stores);
Logger.log(active_stores);*/
if(active_stores>0 && report_stores==active_stores)
{
s.getRange("B5").setValue("Store Location");
s.getRange("C5").setValue("Available Items");
s.getRange("D5").setValue("Orders");
s.getRange("E5").setValue("Delivered Orders");
s.getRange("F5").setValue("Units");
s.getRange("G5").setValue("Total Value");
s.getRange("H5").setValue("Net Sales");
s.getRange("I5").setValue("Average Order Value");
s.getRange("J5").setValue("Items Per Order");
s.getRange("K5").setValue("Processing Time");
s.getRange("L5").setValue("Out Of Stock %");
s.getRange("M5").setValue("Net Cancel %");
s.getRange("N5").setValue("Business Loss");
s.getRange("O5").setValue("Orders With Atleast 1 Item Cancelled %");
s.getRange("P5").setValue("Replacement Given %");
s.getRange("Q5").setValue("Replaced %");
s.getRange("R5").setValue("On Sale SKUs");
s.getRange("S5").setValue("Sale SKUs Sold");
s.getRange("T5").setValue("Sale Items Sold");
var email_data = ed.getRange(i,3,1,4).getValues();
mailmerge.getRange(mailmerge.getLastRow()+1,1,1,4).setValues(email_data);
ed.getRange("K2").setValue(ed.getRange("F"+i).getValue());
/*Logger.log(mailmerge.getLastRow()+1);
Logger.log(email_data);*/
var srow = ss.getSheetByName("Store_Summary").getLastRow();
ss.getSheetByName("Store_Summary").getRange("A1:U"+srow);
main();
}
}
}
above script filter out the data for each store and paste that into report sheet. and than i am trying to run the function which creates the image from range. However it’s not working because it requires me to keep the sheet open, which is very well mentioned in the answer of the author. putting below the script of Mr. @Tanaike which converts a range into an image.
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 ss = SpreadsheetApp.getActiveSpreadsheet();
var name_sheet = ss.getSheetByName(Emails).getRange("K2").getDisplayValue();
const blob = Utilities.newBlob(Utilities.base64Decode(data), MimeType.PNG, name_sheet+".png");
return DriveApp.createFile(blob).getId();
}
<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: 3});
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(), offset: 1 };
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>