Google form to Google doc

[Antoine fontaine]

Hi everyone,

I hope you're doing well. I'm facing a challenge with Google Apps Script and document generation, and I'm hoping someone can provide some guidance.

I have a Google Form that collects various pieces of information. Based on the responses provided in the form, I want to automatically generate a Google Document using a template and replace specific placeholders with the collected data.

I have managed to write a script that accomplishes most of this. It takes the form responses and replaces placeholders in the template document with the corresponding values. However, I'm stuck on one aspect: I need to dynamically determine which placeholders to remove from the document if their corresponding form responses are empty.

For example, if a certain form question wasn't answered, I want to remove the associated placeholder and any related content from the generated document.

here my current code : function autoFillGoogleDocFromForm(e) {

var PurchaseRequestReference = e.values[1];

var Requester = e.values[2];

var CheckedApprovedby = e.values[3];

var Requestdate = e.values[4];

var Purchaseunder = e.values[5];

var SelecttheCostcenter = e.values[6];

var Selectyourproject = e.values[7];

var Budgeted = e.values[8];

var Screenshotofvalidatedlineatbudgettrackerofthebudgetedcost = e.values[9];

var Suppliername = e.values[10];

var SupplierStatus = e.values[11];

var Consultationcriteriaresult = e.values[12];

var Briefneeddescription = e.values[13];

var AmountVATexl = e.values[14];

var Category = e.values[15];

var Subcategory = e.values[16];

var Criticityonrequesteddeliverydate = e.values[17];

var IfYESWhatisthelatestacceptabledeliverydate = e.values[18];

var DeliverablesDocumentstobestatedontheordertothesupplier = e.values[19];

var SpecificrequirementsifapplicablenotspecifiedonthespecandorCADKeyorcriticalpointsSpecialhandlingQualityrequirementsApplicabledocuments = e.values[20];

var DeliveryAddress = e.values[21];

var Purchasetype = e.values[24];

var AmountVATexlspecifycurrency = e.values[25];

var Periodofspending = e.values[26];

var SupplierName = e.values[27];

var Reasonforthedeviation = e.values[28];

var Quotation = e.values[29];

var Departement = e.values[30];

var templatefile = DriveApp.getFileById("1N8cmmM0yzRTnsxY1w7JMOqM4UlxqhyYrGJ-DdxrvnZQ");

var templateResponsefolder = DriveApp.getFolderById("1q3K47rgnXsvUX0Pb_QW1Mo7YWHcuUlA2");

var copy = templatefile.makeCopy(PurchaseRequestReference, templateResponsefolder);

var doc = DocumentApp.openById(copy.getId());

var body = doc.getBody();

body.replaceText("{{PurchaseRequestReference}}", PurchaseRequestReference);

body.replaceText("{{Requester}}", Requester);

body.replaceText("{{Checked/Approvedby}}", CheckedApprovedby);

body.replaceText("{{Requestdate}}", Requestdate);

body.replaceText("{{Purchase under}}", Purchaseunder);

body.replaceText("{{Select the Cost center}}", SelecttheCostcenter);

body.replaceText("{{Selectyourproject}}", Selectyourproject);

body.replaceText("{{Budgeted}}", Budgeted);

body.replaceText("{{Screenshotofvalidatedlineatbudgettrackerofthebudgetedcost}}", Screenshotofvalidatedlineatbudgettrackerofthebudgetedcost);

body.replaceText("{{Suppliername}}", Suppliername);

body.replaceText("{{SupplierStatus}}", SupplierStatus);

body.replaceText("{{Consultationcriteriaresult}}", Consultationcriteriaresult);

body.replaceText("{{Briefneeddescription}}", Briefneeddescription);

body.replaceText("{{AmountVATexl}}", AmountVATexl);

body.replaceText("{{Category}}", Category);

body.replaceText("{{Subcategory}}", Subcategory);

body.replaceText("{{Criticityonrequesteddeliverydate}}", Criticityonrequesteddeliverydate);

body.replaceText("{{IfYESWhatisthelatestacceptabledeliverydate}}", IfYESWhatisthelatestacceptabledeliverydate);

body.replaceText("{{DeliverablesDocumentstobestatedontheordertothesupplier}}", DeliverablesDocumentstobestatedontheordertothesupplier);

body.replaceText("{{SpecificrequirementsifapplicablenotspecifiedonthespecandorCADKeyorcriticalpointsSpecialhandlingQualityrequirementsApplicabledocuments}}", SpecificrequirementsifapplicablenotspecifiedonthespecandorCADKeyorcriticalpointsSpecialhandlingQualityrequirementsApplicabledocuments);

body.replaceText("{{DeliveryAddress}}", DeliveryAddress);

body.replaceText("{{Purchasetype}}", Purchasetype);

body.replaceText("{{AmountVATexlspecifycurrency}}", AmountVATexlspecifycurrency);

body.replaceText("{{Periodofspending}}", Periodofspending);

body.replaceText("{{SupplierName}}", SupplierName);

body.replaceText("{{Reasonforthedeviation}}", Reasonforthedeviation);

body.replaceText("{{Quotation}}", Quotation);

body.replaceText("{{Departement}}", Departement);

var placeholdersToRemove = getPlaceholdersToRemove(e);

placeholdersToRemove.forEach(function(placeholder) {

body.replaceText(placeholder, "");

});

doc.saveAndClose();

}

function getPlaceholdersToRemove(e) {

var placeholders = [

"{{Screenshotofvalidatedlineatbudgettrackerofthebudgetedcost}}",

"{{SpecificrequirementsifapplicablenotspecifiedonthespecandorCADKeyorcriticalpointsSpecialhandlingQualityrequirementsApplicabledocuments}}"

// ... Ajouter d'autres placeholders ici

];

var headers = e.range.getSheet().getRange(1, 1, 1, e.range.getLastColumn()).getValues()[0];

var placeholdersToRemove = [];

placeholders.forEach(function(placeholder) {

var columnIndex = headers.indexOf(placeholder.replace("{{", "").replace("}}", ""));

if (columnIndex !== -1 && !e.values[columnIndex]) {

placeholdersToRemove.push(placeholder);

}

});

return placeholdersToRemove;

}

[Brian Pugh]

Try the extension called, "AutoCrat (New Visions)."

Free and wonderful to use!

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8ceccfa7-4aa3-48be-9e0d-78e2a7df704bn%40googlegroups.com.

[Michael O'Shaughnessy]

OK, first +1 to Autocrat... great Add On for sheets...

However, it sounds like your code is working as you want it to ACCEPT it leaves some placeholder text. Have you tried to look to do something like "if the value is missing or null then set value to empty string".

You could use a ternary operator so change this:

var Suppliername = e.values[10];

to something like this:

var Suppliername = (e.values[10]) ? e.values[10]: "";

This will look at e.values[10] and if it is anything other than "null, undefined, NaN, empty string, false or 0" then it is TRUE so Suppliername will be whatever the value is. If not, the Suppliername will be set to an empty string.

Just a thought....

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJBn2DQ%2B4Bs1BJqNaFqtKPuMpN4GLsa1j7wt5A4WAOjEjfbdwA%40mail.gmail.com.

[Antoine fontaine]

https://docs.google.com/document/d/1LA2NnsZ1W96YqE2xapC5svLETaXAVzF99Y0dhdu3-MA/edit#heading=h.dvf7spdmzg1 I consistently employ this template for the layout, and even when the value is null, it persists within the document.

[Antoine fontaine]

this seems fine but the problem I have is that my template is defined and the answers to the google form vary which means that some variables will not be answered and for this reason I want to delete these empty variables in my google doc. 

[James Fyfe]

Hello everyone, We have also built a free add-on for this use case called Portant: https://workspace.google.com/marketplace/app/portant_data_merge/1016888648275

(Some people say it's easier to use than Autocrat)

Please let me know if you have any questions :)