App Scrips for simple mail merge - google sheets to google docs

[Sean Rooney]

Hi, I am trying to write a script that will perform a mail merge from google sheets to a pre-built template in google docs.

When I run the script, I get an error or line 45 (see screen shot) and can't figure out why. The error is ReferenceError: template is not defined

Can anyone help me. I am new to scripting....

function generateLeaseOptionLetters() {

// Replace with your Google Sheet and Google Docs template IDs

const spreadsheetId = '1pa3MCju0qvxcFWqE8A4JDId-60XPc6qtpN_FUaK1hjA';

const templateId = '1HyzfDRMpqs3T7b8LmEOlTnVf-NVHhppVjLHr6Ee4fLc';

//var templateId = 'HyzfDRMpqs3T7b8LmEOlTnVf-NVHhppVjLHr6Ee4fLc';

// Replace with the name of the sheet that contains your data

const sheetName = 'CF REI Prospects';

// Replace with the index of the first row of data in your sheet

const startRow = 2;

// Get the Google Sheet and the Google Docs template

const spreadsheet = SpreadsheetApp.openById(spreadsheetId);

const sheet = spreadsheet.getSheetByName(sheetName);

// const template = DriveApp.getFileById(templateId).makeCopy().setName('Lease Option Offer Template');

var templateFile = DriveApp.getFileById('1HyzfDRMpqs3T7b8LmEOlTnVf-NVHhppVjLHr6Ee4fLc');

// Get the data from the sheet

const dataRange = sheet.getDataRange();

const dataValues = dataRange.getValues();

// Get the headers from the sheet

const headers = dataValues[0];

// Loop through each row of data and merge the fields with the template

for (let i = startRow - 1; i < dataValues.length; i++) {

const rowData = dataValues[i];

const firstName = rowData[headers.indexOf('First Name')];

const lastName = rowData[headers.indexOf('Last Name')];

const phone = rowData[headers.indexOf('Phone')];

const email = rowData[headers.indexOf('email')];

const tags = rowData[headers.indexOf('Tags')];

const address = rowData[headers.indexOf('address')];

const city = rowData[headers.indexOf('city')];

const state = rowData[headers.indexOf('state')];

const postalCode = rowData[headers.indexOf('postal code')];

const price = rowData[headers.indexOf('Price')];

const rentZestimate = rowData[headers.indexOf('Rent Zestimate')];

const downPayment = rowData[headers.indexOf('Down Payment')];

const zestimate = rowData[headers.indexOf('Zestimate')];

const notes = rowData[headers.indexOf('Notes')];

// Replace the fields in the template with the data from the sheet

const templateFile = DocumentApp.openById(template.getId());

//var templateFile = DocumentApp.openById(template.getId());

const body = templateFile.getBody();

body.replaceText('{{First Name}}', firstName);

body.replaceText('{{Last Name}}', lastName);

body.replaceText('{{Phone}}', phone);

body.replaceText('{{email}}', email);

body.replaceText('{{Tags}}', tags);

body.replaceText('{{address}}', address);

body.replaceText('{{city}}', city);

body.replaceText('{{state}}', state);

body.replaceText('{{postal code}}', postalCode);

body.replaceText('{{Price}}', price);

body.replaceText('{{Rent Zestimate}}', rentZestimate);

body.replaceText('{{Down Payment}}', downPayment);

body.replaceText('{{Zestimate}}', zestimate);

body.replaceText('{{Notes}}', notes);

// Create a new document from the template and email it to hcv...@gmail.com

// const newDocFile = templateFile.makeCopy();

var newFile = templateFile.makeCopy('NEW_FILE_NAME');

const newDocId = newDocFile.getId();

const newDocUrl = newDocFile.getUrl();

GmailApp.sendEmail('em...@gmail.com', 'Lease Option Offer', '', {attachments:[newDocFile.getAs(MimeType.PDF)]});

}

}

[cbmserv...@gmail.com]

The error is saying you have not defined the variable template.

 

In your statement, you are using template.getId() but template is not defined anywhere. I assume you meant to use the variable templateId instead?

--
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/b4ff8067-dbc0-41b2-89c3-89904316b0d2n%40googlegroups.com.

[Jasper Duizendstra]

Hi Sean,

Welcome to scripting :-)

On line 45 you are using a variable, however it does not exist yet, it is commented out (see the first screenshot). You need to check that line and make it active by removing the // (the // makes it a comment, and it will not be excited anymore)

Met vriendelijke groet / Kind regards,

Jasper Duizendstra

Technical Architect & Consultant

Mobile/Signal: +31 6 29 38 40 04 (Netherlands, GMT+2)

E-mail & Meet: jas...@duizendstra.com
Google Cloud Architect | Google Developer Expert

[CBMServices Web]

This is most likely because templatefile is not defined. So make sure you have a statement above this somewhere that defines the templatefile variable to something that have makecopy as a method.

On Fri., Mar. 31, 2023, 9:05 p.m. Sean Rooney, <seanz...@gmail.com> wrote:

Thanks for the advise so far.  I am making progress but I am now seeing a new error stating on line 65

const newDocFile = templateFile.makeCopy('NEW_FILE_NAME');

but receive the error: TypeError: templateFile.makeCopy is not a function

can you help me figure out how to work through this?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1a80cb7f-20c6-454d-b136-d123a92a9254n%40googlegroups.com.