skip to Main Content

I am still quite new to the google Apps Script, but I would really appreciate help with this!

I am writing a google apps script that is to create certificates when a google forms is submitted and send it automatically to the user. The script is taking the newly added row to the sheets that is connected to the Google forms and sends an email then takes the 4th column which is the name of the participant to create a costume certificate. The automatic part is giving me some trouble. I have set up a trigger function (OnForm()) to automatically use the first slide from a presentation ( as a template) and create another slide with the name of the participant, I named this function as sheetToSlide. My next step is to convert this slide to a png and save it, I named this function createPNG. it works when you run it manually, but does not run when I call it within the trigger function after the sheetToSlide function. My theory is that the issue could be with asynchronicity, and that is because when I tried commenting out the sheetToSlide function, the createPNG function worked perfectly.



async function sheetToSlide(sheetData) {
    var name = sheetData[4];
    var presentation = SlidesApp.getActivePresentation();
    var slideTemplate = presentation.getSlideById('p'); //ID for certificate template
    //Logger.log(slideTemplate);
    var newSlide = slideTemplate.duplicate();
    var newSlideID = newSlide.getObjectId();
    var shapes = presentation.getSlideById(newSlideID).getShapes();
    shapes.forEach(function (shape) {
      shape.getText().replaceAllText('{{name}}', name);
    });
    var numberOfSlides = presentation.getSlides().length;
    newSlide.move(numberOfSlides); // last row = last slide
}



async function createPNG() {
    var presentation = SlidesApp.getActivePresentation();
    var presentationId = presentation.getId();
    var parameters = {
      method: "GET",
      headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      contentType: "application/json",
    };
    var slides = presentation.getSlides();
    var lastSlide = slides[slides.length - 1];
    var slideId = lastSlide.getObjectId();
    var url = "https://slides.googleapis.com/v1/presentations/" + presentationId + "/pages/" + slideId + "/thumbnail";
    var response = JSON.parse(UrlFetchApp.fetch(url, parameters)); // sending message to web server to perform that actions resulting in an HTTPResponse object
    var blob = UrlFetchApp.fetch(response.contentUrl, parameters).getBlob(); // fetch image thumbnail and then blob is used to convert it into binary data
    var image = DriveApp.createFile(blob).setName("test12" + ".png"); //file name for each slide
    var imageId = image.getId();
    var folder = DriveApp.getFolderById('14gY2e12mcCYRCuTPP_RLNQ9hE7AKIg1h')
    DriveApp.getFileById(imageId).moveTo(folder);

}



function createOnFormSubmitTrigger() {
  ScriptApp.newTrigger('sendEmailOnFormSubmit')
    .forSpreadsheet('1Uwas2AN5L5-487Bu8NGYZYq3WZ8PlcVd2wSyJtHZgfI')
    .onFormSubmit()
    .create();
  //.forForm('1G1Yjm6JH_3jy5fx-AuURpOxnzFi1E-uatnv-t1pEaiE')
}



async function sendEmailOnFormSubmit(e) { 
    const ss = SpreadsheetApp.openById('1Uwas2AN5L5-487Bu8NGYZYq3WZ8PlcVd2wSyJtHZgfI'); // Access the sheet
    const sheet = ss.getSheetByName('Form Responses 1');
    const lastRow = sheet.getLastRow();
    const rowRange = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn());
    const rowData = rowRange.getValues()[0];
    const name = rowData[4];
    const recipient = e.response.getRespondentEmail();
    const subject = 'Test';
    const body = 'I am testing this. Score: ' + name + ' (' + typeof name + ')';
    GmailApp.sendEmail(recipient, subject, body);
    await sheetToSlide(rowData);
    await createPNG();
    //Utilities.sleep(30000); //30 seconds
    //GmailApp.sendEmail(recipient, subject, body);


}

I have tried multiple things. I tried putting a delay between the two functions using the utilities.sleep(), i have also tries using the async/await objects as you can see in the code. However, non of this worked. I have also tried putting the two functions into one.

I would appreciate any help or advice as I have been stuck on this for too long.

Thank you

2

Answers


  1. You need to return something from an async function (or throw an error inside it), otherwise it behaves as if it where a forever-pending-promise.

    According to MDN:

    Await expressions make promise-returning functions behave as though they’re synchronous by suspending execution until the returned promise is fulfilled or rejected

    So you need to return something. I would say it doesn’t matter what you return, just that it needs to literally have return at the end of the function body.

    See here

    Login or Signup to reply.
  2. First, in the current stage, the methods of Google Apps Script are run with a synchronous process. So, I think that in your script, async/await is not required to be used. And, about but does not run when I call it within the trigger function after the sheetToSlide function., I think that your script runs createPNG(). But, in this case, I think that the modification of Google Slide in sheetToSlide might not be reflected in the Google Slide. If my understanding is correct, how about the following modification?

    Modified script:

    Please modify sheetToSlide as follows.

    From:

        newSlide.move(numberOfSlides); // last row = last slide
    }
    

    To:

        newSlide.move(numberOfSlides); // last row = last slide
        presentation.saveAndClose(); // <--- Added
    }
    

    When your showing whole script is modified, it becomes as follows.

    function sheetToSlide(sheetData) {
      var name = sheetData[4];
      var presentation = SlidesApp.getActivePresentation();
      var slideTemplate = presentation.getSlideById('p'); //ID for certificate template
      //Logger.log(slideTemplate);
      var newSlide = slideTemplate.duplicate();
      var newSlideID = newSlide.getObjectId();
      var shapes = presentation.getSlideById(newSlideID).getShapes();
      shapes.forEach(function (shape) {
        shape.getText().replaceAllText('{{name}}', name);
      });
      var numberOfSlides = presentation.getSlides().length;
      newSlide.move(numberOfSlides); // last row = last slide
    
      presentation.saveAndClose(); // <--- Added
    }
    
    function createPNG() {
      var presentation = SlidesApp.getActivePresentation();
      var presentationId = presentation.getId();
      var parameters = {
        method: "GET",
        headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
        contentType: "application/json",
      };
      var slides = presentation.getSlides();
      var lastSlide = slides[slides.length - 1];
      var slideId = lastSlide.getObjectId();
      var url = "https://slides.googleapis.com/v1/presentations/" + presentationId + "/pages/" + slideId + "/thumbnail";
      var response = JSON.parse(UrlFetchApp.fetch(url, parameters)); // sending message to web server to perform that actions resulting in an HTTPResponse object
      var blob = UrlFetchApp.fetch(response.contentUrl, parameters).getBlob(); // fetch image thumbnail and then blob is used to convert it into binary data
      var image = DriveApp.createFile(blob).setName("test12" + ".png"); //file name for each slide
      var imageId = image.getId();
      var folder = DriveApp.getFolderById('14gY2e12mcCYRCuTPP_RLNQ9hE7AKIg1h')
      DriveApp.getFileById(imageId).moveTo(folder);
    
    }
    
    function createOnFormSubmitTrigger() {
      ScriptApp.newTrigger('sendEmailOnFormSubmit')
        .forSpreadsheet('1Uwas2AN5L5-487Bu8NGYZYq3WZ8PlcVd2wSyJtHZgfI')
        .onFormSubmit()
        .create();
      //.forForm('1G1Yjm6JH_3jy5fx-AuURpOxnzFi1E-uatnv-t1pEaiE')
    }
    
    function sendEmailOnFormSubmit(e) {
      const ss = SpreadsheetApp.openById('1Uwas2AN5L5-487Bu8NGYZYq3WZ8PlcVd2wSyJtHZgfI'); // Access the sheet
      const sheet = ss.getSheetByName('Form Responses 1');
      const lastRow = sheet.getLastRow();
      const rowRange = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn());
      const rowData = rowRange.getValues()[0];
      const name = rowData[4];
      const recipient = e.response.getRespondentEmail();
      const subject = 'Test';
      const body = 'I am testing this. Score: ' + name + ' (' + typeof name + ')';
      GmailApp.sendEmail(recipient, subject, body);
      sheetToSlide(rowData);
      createPNG();
      //Utilities.sleep(30000); //30 seconds
      //GmailApp.sendEmail(recipient, subject, body);
    }

    Reference:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search