Change cell Data on Spreadsheet

September 14, 2022

I am trying to take a template spreadsheet, and copy it, then change the data and export as pdf, finally email it.

So far I can do everything except change it on the sheet. It even changes in the logger. Can someone help with this?

Code as follows:

function After_Submit(e, values){
    var range = e.range;
    var row = range.getRow(); //get the row of newly added form data
    var sheet = range.getSheet(); //get the Sheet
    var headers = sheet.getRange(1, 1, 1,5).getValues().flat(); //get the header names from A-O
    var data = sheet.getRange(row, 1, 1, headers.length).getValues(); //get the values of newly added form data + formulated values
    var values = {}; // create an object 
    for( var i = 0; i < headers.length; i++ ){
      values[headers[i]] = data[0][i]; //add elements to values object and use headers as key
    }
  
    
    const pdfFile = Create_PDF(values);
    sendEmail(e.namedValues['Your Email'][0],pdfFile);
  }
  
  function sendEmail(email,pdfFile,){
    
    GmailApp.sendEmail(email, "Subject", "Message", {
      attachments: [pdfFile], 
      name: "From Someone"
  
    });
   
  }
 function Create_PDF(values, ) {
    const PDF_folder = DriveApp.getFolderById("1t_BYHO8CqmKxVIucap_LlE0MhslpT7BO");
    const TEMP_FOLDER = DriveApp.getFolderById("1TNeI1HaSwsloOI4KnIfybbWR4u753vVd");
    const PDF_Template = DriveApp.getFileById('1Ye7DyJQOjA3J_EUOQteWcuASBCfqlA-_lzyNw0REjY8');
    
    const newTempFile = PDF_Template.makeCopy(TEMP_FOLDER);
    const  openDoc = SpreadsheetApp.openById(newTempFile.getId());
    const newOpenDoc = openDoc.getSheetByName("Sheet1");
    var body = newOpenDoc.getDataRange().getValues();
    for (const key in values) {
    body = body.toString().replace("{{"+key+"}}", values[key]);
    }
    Logger.log(body);
    newOpenDoc.getDataRange().setValues(body);
  
    SpreadsheetApp.flush();
  
    const BLOBPDF = newTempFile.getAs(MimeType.PDF);
    const pdfFile =  PDF_folder.createFile(BLOBPDF);
    console.log("The file has been created ");
    return pdfFile;
}

>Solution :

When I saw your script, in the for loop of the function Create_PDF, an array retrieved from the sheet is converted to a string. I thought that this might be the reason for your issue.

When this issue was removed, how about the following modification?

From:

var body = newOpenDoc.getDataRange().getValues();
for (const key in values) {
body = body.toString().replace("{{"+key+"}}", values[key]);
}

To:

values = Object.fromEntries(Object.entries(values).map(([k, v]) => [`{{${k}}}`, v]));
var body = newOpenDoc.getDataRange().getValues().map(r => r.map(c => values[c] || c));