Generating Google Document using Data in Google Sheets

[David De La Fuente]

Hello!

I have the following code that generates a Google Doc and replaces a bunch of placeholders with data in a Google Sheet. I works perfectly fine for the body of the document, but for some reason the placeholder that is in the footer of the document does not get replaced. I assumed that the replacing footer text would be the same as replacing body text. Is there additional or different code that I need to be using?

function createDoc() {

  const doc = DriveApp.getFileById('DOCUMENT ID');

  const docFolder = DriveApp.getFolderById('FOLDER ID')

  const docSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Source')

  const docRows = billingSheet.getDataRange().getValues();

  

//Used to skip the header in Google Sheets and skip any rows that do not have an "X" in a specified column

  billingRows.forEach(function(row, index){

    if (index === 0) return; 

    if (row[2]) return;

  

    const docCopy = doc.makeCopy(`${row[0]} Request` , billingTempFolder);

    const docDoc = DocumentApp.openById(docCopy.getId());

    const docBody = docDoc.getBody(); //this works at replacing the text as listed below

const docFooter = docDoc.getFooter();  //this does not work the same way as replacing the body

docFooter.replacetext('{{Reference Number}}', row[0]);

    docBody.replaceText('{{Reference Number}}', row[0]);

    docBody.replaceText('{{Version}}', row[3]);

    docBody.replaceText('{{Name}}', row[4]);

    billingBody.replaceText('{{State}}', row[5]);

docDoc.saveAndClose();

If anyone has any suggestions, I would greatly appreciate it. Thank you!!

[David De La Fuente]

Correction, this is the code:

function createDoc() {

  const doc = DriveApp.getFileById('DOCUMENT ID');

  const docFolder = DriveApp.getFolderById('FOLDER ID')

  const docSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Source')

  const docRows = docSheet.getDataRange().getValues();

  

//Used to skip the header in Google Sheets and skip any rows that do not have an "X" in a specified column

  docRows.forEach(function(row, index){

    if (index === 0) return; 

    if (row[2]) return;

  

    const docCopy = doc.makeCopy(`${row[0]} Request` , docFolder);

    const docDoc = DocumentApp.openById(docCopy.getId());

    const docBody = docDoc.getBody(); //this works at replacing the text as listed below

const docFooter = docDoc.getFooter();  //this does not work the same way as replacing the body

docFooter.replacetext('{{Reference Number}}', row[0]);

    docBody.replaceText('{{Reference Number}}', row[0]);

    docBody.replaceText('{{Version}}', row[3]);

    docBody.replaceText('{{Name}}', row[4]);

    docBody.replaceText('{{State}}', row[5]);

docDoc.saveAndClose();

I had "billing" in a few places from a different version I had of the script.

[Ed Sambuco]

Code should read  var docFooter = docDoc.addFooter();      nog getFooter()  Google docs always have bodies, but not necessarily headers or footers.

--
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/2c9fb3c8-fd1d-45c2-90c5-1219f732eb75n%40googlegroups.com.

[David De La Fuente]

Thank you so much for your email. I tried using this but I am getting the error: "Document already contains a footer." I removed the footer from my template document but still get the error. 

const docCopy = docDWQ.makeCopy(`${row[0]} Request` , docTempFolder);

    const docDoc = DocumentApp.openById(docCopy.getId());

    const docBody = docDoc.getBody();  

    var docFooter = docDoc.addFooter();

docFooter.replacetext('{{Reference Number}}', row[0]);

    docBody.replaceText('{{Reference Number}}', row[0]);

    docBody.replaceText('{{Version}}', row[3]);

    docBody.replaceText('{{Name}}', row[4]);

    docBody.replaceText('{{State}}', row[5]);

Am I missing something?

[Ed Sambuco]

Hmmm .. you have replacetext rather than replaceText for the footer update ... that won't do.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cb7566f1-26dc-453b-b9c7-b851204f9d94n%40googlegroups.com.

[David De La Fuente]

Sorry, that was a typo when I was typing it into here - it is replaceText in the script but it still doesn't work

[Gurleen Virk]

Hi David, 

Hopefully it's okay to ask a question on top of your question. I'm very new to Apps Script and I'm looking for how to take specific data from Google Sheets and have it added to a copied version of a Google Doc template. My goal is to have each row in my Google Sheet generate a new copy of a Docs template. Any chance you don't mind sharing your process on how you did this and breakdown of your script? Really really appreciate it! 

-Gurleen

[Phillip Bainbridge]

Hi Gurleen

I have an example of something that does that on my blog, with a quick tutorial video on how to use it. The code is fully accessible and includes comments throughout to help explain. https://www.pbainbridge.co.uk/2020/05/bulk-create-google-docs-from-google.html

Kind regards

Phil

[Gurleen Virk]

Thank you, Phil! 

[Jeremy Dutton]

I had the same issue. As I was using a template I had created, I removed the footer, then coded the script to add it, and then append a paragraph with the desired text.  Probably not the best/most efficient way to do it but it worked.  

The only problem I am now having is accessing said text and formatting it. For some reason I cannot get my head around how to do that (other than trying to retrieve the footer and paragraphs as child objects maybe?). Is anyone aware if there is any documentation on the DOM for a google doc specifically? I haven't been able to find any so far. 

[DimuDesigns]

@JeremyDutton The Google Docs API has a section describing document structure.

See link below:

https://developers.google.com/docs/api/concepts/structure