CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
nonomg
nonomg

Reputation: 19

Trigger a function when a checkbox is checked in AppsScript

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 😅

Upvotes: 0

Views: 1431

Answers (1)

Cooper
Cooper

Reputation: 64040

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);
  }
}

Upvotes: 1

Related Questions