Export Table to Image using a checkbox

[Claude Toussain]

First of all, I'd like to apologize for my poor English.

I'm still a beginner with App Script but after but I did manage to get a script taking a screenshot of a Sheet table to work.

I found some infos and code on the internet, I'm pretty sure it can be improved.

It  takes data from this table

and turns it into a PNG file on my Drive

The first version was able to do this even from the mobile app using a checkbox on another sheet.

I updated the file and the script to add some functions (change background colors, export font color, better PNG name,...)

The new script works fine on a computer if I launch it manually (App Script page or in the Sheet macro menu, but always stops when I use the checkbox (E2 cell on the Table sheet)

I tried to improve th script, and I think I made it lighter than the first version but it keeps freezing when using Drive Service.

The code

function SaveSS() //OK 2025/09/02

{

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

spreadsheet.toast('Creation et sauvegarde image', 'Automation', -1);

var sheetSS = spreadsheet.getSheetByName("SS");

var dateSS = sheetSS.getRange('B1').getValue();

dateSS = Utilities.formatDate(dateSS, "GMT+23:00", "yyyy-MM-dd");

var voieSS = sheetSS.getRange('C1').getValue();

var sensSS = sheetSS.getRange('D1').getValue();

var lastRSS = sheetSS.getLastRow();

if(lastRSS < 3)

{

spreadsheet.toast('Pas de données', 'Automation', -1);

return;

};

var rangeSS =sheetSS.getRange(1,1,lastRSS,4);

var dataSS = rangeSS.getValues();

dataSS[0][1] = dateSS;

var fcSS = rangeSS.getFontColors();

var bgSS = sheetSS.getRange(1,1,lastRSS,1).getBackgrounds();

var fsSS = rangeSS.getFontSizes();

var widthIMG=0;

var cwSS = [];

for(var i=1;i<=4;i++)

{

widthIMG += sheetSS.getColumnWidth(i)*0.8;

cwSS.push(sheetSS.getColumnWidth(i));

};

var heightIMG = 14;

var rhSS = [];

for (var k=1;k<=lastRSS;k++)

{

heightIMG += sheetSS.getRowHeight(k)+2;

rhSS.push(sheetSS.getRowHeight(k));

fsSS.push(sheetSS.getRange(k,1).getFontSize);

}

var html = "<table border='1'>"

spreadsheet.toast('Creation HTML', 'Automation', -1);

//Creation de la table

for (i = 0; i < lastRSS; i++)

{

html += "<tr>"

for (var j = 0; j < 4; j++)

{

//html += "<td>" + dataSS[i][j] + "</td>";

html += "<td style='height:"+rhSS[i]+"px;width:"+ cwSS[j]*0.8 + "px;background:" + bgSS[i][0] + ";color:" + fcSS[i][j] + ";font-size:"+fsSS[i]+"px;'>" + dataSS[i][j] + "</td>";

//html += "<td style='height:"+sheetSS.getRowHeight(i+1)+"px;width:"+ sheetSS.getColumnWidth(j+1)*0.8 + "px;background:" + bgSS[i][j] + ";color:" + fcSS[i][j] + ";font-size:"+fsSS[i][j]+"px;'>" + dataSS[i][j] + "</td>";

}

html += "</tr>";

}

html += "</table>"

spreadsheet.toast('Creation image', 'Automation', -1);

//Creation de l'image

const img = Charts.newTableChart().setDataTable(Charts.newDataTable().addColumn(Charts.ColumnType.STRING, '').addRow([html]).build()).setOption('allowHtml',true).setDimensions(widthIMG,heightIMG).build();

spreadsheet.toast('sauvegarde image', 'Automation', -1);

//Sauvegarde de l'image

const blob = img.getAs('image/png');

blob.setName("TOP "+dateSS+" "+voieSS+" "+sensSS+".png");

spreadsheet.toast('nom du blob', 'Automation', -1);

const folder = DriveApp.getFolderById('xxxxx');

folder.createFile(blob);

spreadsheet.toast('ecriture', 'Automation', -1);

};

I've tried to make it as light as possible with

html += "<td>" + dataSS[i][j] + "</td>";

but it didn't worked (still working with manual launch). It always stops at

const folder = DriveApp.getFolderById('xxxxx');

even if I place it at the beginning of the script.

Thank you for reading me.

PS: SS stands for ScreenShot :D

[Kildere S Irineu]

O problema é gatilho simples com checkbox.

Quando a checkbox edita a célula E2, provavelmente o script roda via onEdit(e). Esse tipo de gatilho roda com restrições e não pode usar serviços que exigem autorização, como DriveApp. Por isso funciona manualmente, mas trava em:

const folder = DriveApp.getFolderById('xxxxx');

O próprio relato diz que manual funciona, mas pela checkbox para no DriveApp . A documentação confirma que gatilhos simples têm limitações, enquanto gatilhos instaláveis têm mais capacidades .

Use um gatilho instalável On edit:

function onEditInstalled(e) {
const range = e.range;
const sheet = range.getSheet();

// Só executa quando editar E2 da aba Table
if (sheet.getName() !== 'Table') return;
if (range.getA1Notation() !== 'E2') return;
if (range.getValue() !== true) return;

SaveSS();

// Desmarca a checkbox depois de executar
range.setValue(false);
}

Depois, no Apps Script:

Triggers / Acionadores → Add trigger → função onEditInstalled → From spreadsheet → On edit

Também corrija esta linha no código original:

fsSS.push(sheetSS.getRange(k, 1).getFontSize);

para:

fsSS.push(sheetSS.getRange(k, 1).getFontSize());

Melhor ainda: como ele já usa rangeSS.getFontSizes(), no HTML use:

font-size:" + fsSS[i][j] + "px;

A causa real não é o tamanho do HTML nem o Charts: é autorização. Manualmente o usuário autoriza; no onEdit simples, DriveApp não pode rodar.