Script generating pdf with old data snapshot from google spreadsheet?

0
votes

Folks, Anybody can tell me why this code sends a pdf with a snapshot of data prior to the update?

What am I missing here? Is there a need to save or flush the current file? Any help is appreciated here.

Tks, Ivan Daudt

function onFormSubmit(e) {
      var responses = e.namedValues;
      var s = SpreadsheetApp.getActiveSheet();  
      var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
      var row = s.getActiveCell().getRow(); //identify last row
      var sourceRange = s.getRange(row,1,1,38); //Get the soure range.

      //open target file and updates its data
      var destFile = SpreadsheetApp.openById("1c8NepY_2ZDxrRVzrhbax4VRPGarqb59eir8wNQ7w4qc");
      SpreadsheetApp.setActiveSpreadsheet(destFile);
      var destSheet = destFile.getSheetByName("dados do aluno");
      var destRange = destSheet.getRange(2,1,1,38); //Gets the destination sheet range.
      destRange.setValues(sourceRange.getValues()); //Gets source sheet value and uses it to set the imported sheet value.
      //send by email
      var sheetNumber = (tab.getIndex()-1);
      var sheetId = sheetNumber ? destFile.getSheets()[sheetNumber].getSheetId() : null;  
      var url_base = destFile.getUrl().replace(/edit$/,'');

      var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
          + (sheetId ? ('&gid=' + sheetId) : ('&id=' + destFile.getId())) 
          // following parameters are optional...
          + '&size=A4'      // paper size
          + '&portrait=true'    // orientation, false for landscape
          + '&fitw=true'        // fit to width, false for actual size
          + '&sheetnames=false&printtitle=false&pagenumbers=true'  //hide optional headers and footers
          + '&gridlines=false'  // hide gridlines
          + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

      var options = {
        headers: {
          'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
        }
      }
      var response = UrlFetchApp.fetch(url_base + url_ext, options);
      var thePdf = response.getBlob().setName('Avaliação '+ onome + '.pdf');  
      MailApp.sendEmail(
        mailtoaddr[0],
        subject,
        message,
        {attachments: [thePdf]});
   }
1
google-apps-scriptpdf-generationgoogle-sheets-api

1 Answers

1
votes
  • You want to know the reason of this code sends a pdf with a snapshot of data prior to the update.

If my understanding is correct, how about this answer?

Modification points:

  • In your script, the data is put to destRange using destRange.setValues(sourceRange.getValues());. Although I'm not sure about tab of var sheetNumber = (tab.getIndex()-1);, if you want to export destSheet of var destSheet = destFile.getSheetByName("dados do aluno"); as a PDF, it is required to put SpreadsheetApp.flush() after destRange.setValues(sourceRange.getValues());. By this, the put values are reflected to the PDF data.
  • From above situation, about Is there a need to save or flush the current file?, it's yes.

Modified script:

When your script is modified, please modify as follows.

destRange.setValues(sourceRange.getValues());

destRange.setValues(sourceRange.getValues());
SpreadsheetApp.flush();  // Added

Reference:

If this was not the direction you want, I apologize.