Trigger a function when a checkbox is checked in AppsScript

Question

function createSlide() { try { let spread = SpreadsheetApp.getActiveSpreadsheet(); let sheet = spread.getSheetByName("Feuille 1"); let values = sheet.getDataRange().getValues(); values.shift(); // remove header let update = false; values.forEach( row => { // if checked row[4] is true or link is present if( ( row[20] == true ) || ( row[21] == "" ) ) { // open the template //SpreadsheetApp.getActiveSpreadsheet().toast("Génération du GSlides en cours...⌛"); let presentation = SlidesApp.openById("1kZ4zCaInca68peo4uKX0MEFLnWuyOdrFz9aRbb1fvIw"); let slides = presentation.getSlides(); // create a new presentation named for the employee presentation = SlidesApp.create("Alvo - BR - " + row[0]); // copy the template slides to the new presentation slides.forEach( (slide,index) => { presentation.insertSlide(index,slide); } ); let newSlides = presentation.getSlides(); newSlides[1].remove(); // there is only one slide slides = presentation.getSlides()[0]; //slides.replaceAllText("{{Vendor}}",row[0]); slides.replaceAllText("{{Description}}",row[15]); //slides.replaceAllText("{{Order#}}",row[2]); //slides.replaceAllText("{{Type}}",row[3]); //slides.replaceAllText("{date of hire}",Utilities.formatDate(row[3],"PST","dd/MM/yyyy")); // update the values row[20] = false; row[21] = presentation.getUrl(); update = true; } } ); // update the spreadsheet if( update ) { values = values.map( row => row.slice(20,22) ); sheet.getRange(2,21,values.length,2).setValues(values); } } catch(err) { console.log(err); } } function onEdit(e) { // check if the edited cell is in column 5 if (e.range.getColumn() == 20) { // check if the value of the edited cell is true if (e.value == true) { // call the createSlide function createSlide(); } } }

Here is my Apps Script code.

The fact is that I would like that my createSlide() function, being executed when a checkbox is checked.

I also want the function to be executed only for the row where the checkbox is checked.

Tried further things, but actually, if I made any changes on the spreadsheet, a Google Slide is generated, and that's not what I want 😅

If you want to check whether the checkbox is checked, please modify e.value == true to e.value === "True" and e.range.isChecked() and test it again. I'm worried that in the case of e.value == true, when the cell value is not empty, it might be true. By the way, if you are using onEdit as the installable OnEdit trigger, I would like to rename the function name and reinstall the renamed function as the installable OnEdit trigger. — Tanaike
– Tanaike, Commented Jan 17, 2023 at 8:46
Thanks for your reply! I now have this code, but I'm sorry I'm not sure understanding correctly the part of your message about the installable OnEdit trigger. Whatever, the updated code seems not to be working, as it still generating me a new Slide template for every modification. function onEdit(e) { if (e.range.isChecked() == 20) { if (e.value === "True") { createSlide(); } } } — nonomg
– nonomg, Commented Jan 17, 2023 at 9:01
Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I'm worried that you might not be able to correctly understand my comment. About the official document of isChecked(), you can see it at here. About I'm not sure understanding correctly the part of your message about the installable OnEdit trigger., my post might be useful. tanaikech.github.io/2018/09/21/… — Tanaike
– Tanaike, Commented Jan 17, 2023 at 11:36

Cooper · Accepted Answer · 2023-01-18 00:08:31Z

Try something like this:

function onEdit(e) { const sh = e.range.getSheet(); if (sh.getName() == "Your sheet name" && e.range.columnStart == 20 && e.value == "TRUE") { createSlide(); } } function createSlide(e) { try { let ss = SpreadsheetApp.getActive(); let sh = ss.getSheetByName("Feuille 1"); let values = sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).getValues(); let update = false; values.forEach( row => { if( ( row[20] == true ) || ( row[21] == "" ) ) { let presentation = SlidesApp.openById("1kZ4zCaInca68peo4uKX0MEFLnWuyOdrFz9aRbb1fvIw"); let slides = presentation.getSlides(); presentation = SlidesApp.create("Alvo - BR - " + row[0]); slides.forEach( (slide,index) => { presentation.insertSlide(index,slide); } ); let newSlides = presentation.getSlides(); newSlides[1].remove(); slides = presentation.getSlides()[0]; slides.replaceAllText("{{Description}}",row[15]); row[20] = false; row[21] = presentation.getUrl(); update = true; } } ); if( update ) { values = values.map( row => row.slice(20,22) ); sh.getRange(2,21,values.length,2).setValues(values); } } catch(err) { console.log(err); } }

Collectives™ on Stack Overflow

Trigger a function when a checkbox is checked in AppsScript

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related