Export Table to Image using a checkbox

Claude Toussain

unread,

Apr 30, 2026, 11:23:30 AM Apr 30

to Google Apps Script Community

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

Capture d’écran du 2026-04-30 14-15-20.png

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

unread,

May 2, 2026, 12:04:55 PM May 2

to Google Apps Script Community

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.

Claude Toussain

unread,

May 6, 2026, 2:14:29 AM May 6

to Google Apps Script Community

Thank you!

It works fine with onEditInstalled().

Also, thank you for the corrections, I used fsSS.push(sheet.getRange(k, 1).getFontSize()) to have a littler Array but now I realize that it calls getFontSize() and getRange() k times instead of geetFontSizes() a single time.

Reply all

Reply to author

Forward