CODEWITHSUNDEEP
google-apps-script
Nate
Nate

Reputation: 35

how to append sheet before next function run

I'm appending a row in a spreadsheet from a form then taking the data that was added to the spreadsheet and populating a document template. From there I'm creating a PDF and emailing it to myself. The problem I'm facing is that the data is always coming from the second to last row of the spreadsheet instead of the newly appended row (from the latest form data). It seems like the appended data is not being saved before the AutoFillDocFromTemplate function runs. What am I missing?

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate();
};

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

/* @Process Form */
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1nz2uIWab1eSirljzvNn6SyNyxz3npDTu4mqVYV0blsU/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  ws.appendRow([formObject.company_name,
                formObject.identity_transformation,
  formObject.character_want,
  formObject.external_problem,
  formObject.internal_problem,
  formObject.philisophical_problem,
  formObject.empathy,
  formObject.authority,
  formObject.plan_step1,
  formObject.plan_step2,
  formObject.plan_step3,
  formObject.direct_cta,
  formObject.transitional_cta,
  formObject.failure,
  formObject.success]);
}
 

/* This function creates a new document from a template and updates the placeholder with info from a Google Sheet*/
function AutofillDocFromTemplate(){
 // Get the spreadsheet & sheet
  var url = "https://docs.google.com/spreadsheets/d/1nz2uIWab1eSirljzvNn6SyNyxz3npDTu4mqVYV0blsU/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url).getSheetByName("Data");

  // Set the range to the last row of data in the Sheet
  var data = ss.getRange(ss.getLastRow(),1,1, ss.getLastColumn()).getValues();  
  // Get the original template Doc
  const templateDoc = DriveApp.getFileById("1yu5jzg4NbRtTy_UjwzBmnpc-3_pNOqA-l1_UVsiAIWQ");
  // Get the folder for where the docs should go
  const folder = DriveApp.getFolderById("1prOQxp5jmDvJqiwIfLbbkLYWoz5QlTUC");
  // Create the new file name
  const newFileName = ("BrandScript")
  // Create a copy of the template doc
  const newTempFile = templateDoc.makeCopy(newFileName, folder);
  // Open the new temp doc
  const openDoc = DocumentApp.openById(newTempFile.getId());
  // Get the body of the new temp doc
  const body = openDoc.getBody();
  // Replace placeholders with spreadsheet data from last row
  body.replaceText("%company_name%", data[0][0]);
  body.replaceText("%identity_transformation%", data[0][1]);
  body.replaceText("%character_want%", data[0][2]);
  body.replaceText("%external_problem%", data[0][3]);
  body.replaceText("%internal_problem%", data[0][4]);
  body.replaceText("%philisophical_problem%", data[0][5]);
  body.replaceText("%empathy%", data[0][6]);
  body.replaceText("%authority%", data[0][7]);
  body.replaceText("%plan_step1%", data[0][8]);
  body.replaceText("%plan_step2%", data[0][9]);
  body.replaceText("%plan_step3%", data[0][10]);
  body.replaceText("%direct_cta%", data[0][11]);
  body.replaceText("%transitional_cta%", data[0][12]);
  body.replaceText("%failure%", data[0][13]);
  body.replaceText("%success%", data[0][14]);
 
  // Save and close the new doc
  openDoc.saveAndClose();
  //Send email with new document
  var message = "Attached is your draft BrandScript"; // Customize message
  var emailTo = "to be inserted" // replace with your email
  var subject = "Your Draft BrandScript"; // customize subject 
  var pdf = DriveApp.getFileById(openDoc.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:'DraftBrandScript.pdf',content:pdf, mimeType:'application/pdf'}; // customize file name: "Autogenerated template"
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
            
}




<script>
  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);    
      
      
  function handleFormSubmit(formObject) {
    google.script.run.processForm(formObject);
    google.script.run.AutofillDocFromTemplate();
    document.getElementById("myForm").reset();
    
  }
  
 
</script>

Upvotes: 1

Views: 49

Answers (1)

Tanaike
Tanaike

Reputation: 201473

I think that the reason of your issue is google.script.run works with the asynchronous process. By this, at the following script,

google.script.run.processForm(formObject);
google.script.run.AutofillDocFromTemplate();

Before processForm is not finished, AutofillDocFromTemplate is run. So in order to remove your issue, I would like to propose the following patterns.

Pattern 1:

In this pattern, withSuccessHandler is used. By this, AutofillDocFromTemplate is run after processForm was run.

From:

google.script.run.processForm(formObject);
google.script.run.AutofillDocFromTemplate();

To:

google.script.run.withSuccessHandler(() => google.script.run.AutofillDocFromTemplate()).processForm(formObject);

Pattern 2:

In this pattern, Google Apps Script is modified. By this, AutofillDocFromTemplate is run after processForm was run.

Google Apps Script side:

From: 
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1nz2uIWab1eSirljzvNn6SyNyxz3npDTu4mqVYV0blsU/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  ws.appendRow([formObject.company_name,
                formObject.identity_transformation,
  formObject.character_want,
  formObject.external_problem,
  formObject.internal_problem,
  formObject.philisophical_problem,
  formObject.empathy,
  formObject.authority,
  formObject.plan_step1,
  formObject.plan_step2,
  formObject.plan_step3,
  formObject.direct_cta,
  formObject.transitional_cta,
  formObject.failure,
  formObject.success]);
  
  AutofillDocFromTemplate() //  <--- Added
}

HTML&Javascript side:

google.script.run.processForm(formObject);
// google.script.run.AutofillDocFromTemplate();  // <--- removed

Note:

  • If the issue was not resolved by above modifications, please try to use SpreadsheetApp.flush().

Reference:

Upvotes: 1

Related Questions