ForEach Loop Help

[Luis Gomez]

I am trying to help my wife with a school project but I don't have enough code experience to make this work. All of the samples I'm finding online are to generate multiple emails, one for each row. She needs a SINGLE email that renders all of column two(B) which would be a list of names from the sheet. Everything works except it is only pulling the name on the first row.

const file = temp.makeCopy(folder);   

const doc = DocumentApp.openById(file.getId());   

const body = doc.getBody();  

rows.forEach((row)=>{     

 

body.replaceText('{{Student or Staff}}',row [1]);         

 })

Thank you!

[Clay Smith]

The first time the loop runs on the rows it takes the first row and replaces all instances of {{Student or Staff}} with the value of the row index 1. The identifier of {{Student or Staff}} is now gone and replaced with a new value of row[1]. All other runs of the iteration can’t find an identifier and so no replacement happens. 

If you are iterating rows to create a document for each student or staff your loop will need to wrap around those operations. 

Knowing more about you goal will help better guide. 

Reference:

https://developers.google.com/apps-script/reference/document/text#replaceText(String,String)

On Jul 1, 2022, at 16:34, Luis Gomez <lago...@gmail.com> wrote:

I am trying to help my wife with a school project but I don't have enough code experience to make this work. All of the samples I'm finding online are to generate multiple emails, one for each row. She needs a SINGLE email that renders all of column two(B) which would be a list of names from the sheet. Everything works except it is only pulling the name on the first row.

--
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/7d8341a4-1e8e-4fb0-bb6a-8875c4323629n%40googlegroups.com.

[Luis Gomez]

Thank you so much for your reply. What we are trying to accomplish is to take the students names from a Google Sheet and put them on a Google Doc list. This list will be automatically sent to all staff members utilizing a trigger. 

Here is an example of how the list looks.

This is the Document template that we need the names to go on.

The code that I posted earlier is making one document each time the loop iterates. I think I need to take the document creation code outside the loop so it only gets created one time. I have been trying all day without success.

Here is the code. Thank you!

function createNewGoogleDocs() {

const googleDocTemplate = DriveApp.getFileById(' ');

const destinationFolder = DriveApp.getFolderById(' ')

const sheet = SpreadsheetApp

.getActiveSpreadsheet()

.getSheetByName('Data')

const rows = sheet.getDataRange().getValues();

rows.forEach(function(row, index){

if (index === 0) return;

const copy = googleDocTemplate.makeCopy("Report" , destinationFolder)

const doc = DocumentApp.openById(copy.getId())

const body = doc.getBody();

body.replaceText('{{Date}}', row[0]);

body.replaceText('{{Student or Staff}}', row[1]);

doc.saveAndClose();

})

} 

[Clay Smith]

Try this 

function createNewGoogleDocs() {

const googleDocTemplate = DriveApp.getFileById(' ');

const destinationFolder = DriveApp.getFolderById(' ')

const sheet = SpreadsheetApp

.getActiveSpreadsheet()

.getSheetByName('Data')

const [head, …rows] = sheet.getDataRange().getValues();

const copy = googleDocTemplate.makeCopy("Report" , destinationFolder)

const doc = DocumentApp.openById(copy.getId())

const body = doc.getBody();

body.replaceText('{{Date}}', row[0]);

body.replaceText('{{Student or Staff}}',rows.join(', ');

doc.saveAndClose();

} 

On Jul 1, 2022, at 17:38, Luis Gomez <lago...@gmail.com> wrote:

Thank you so much for your reply. What we are trying to accomplish is to take the students names from a Google Sheet and put them on a Google Doc list. This list will be automatically sent to all staff members utilizing a trigger. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/58117c86-ecea-4f8e-bc45-2d3dc950c019n%40googlegroups.com.

[Luis Gomez]

We are getting closer. The data is now being pulled for multiple rows but is bringing all of the column data(A,B,C). I am going to do some research to see if I can figure out how to only get the data from column B. Thank you! 

[Clay Smith]

Sorry about that. Change this line. 

body.replaceText('{{Student or Staff}}',rows.map(row=>row[1]).join(‘, ‘)

On Jul 1, 2022, at 18:24, Luis Gomez <lago...@gmail.com> wrote:

We are getting closer. The data is now being pulled for multiple rows but is bringing all of the column data(A,B,C). I am going to do some research to see if I can figure out how to only get the data from column B. Thank you! 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d99ecc93-6efb-4fa9-9948-f174aad7e5d3n%40googlegroups.com.

[Luis Gomez]

This is perfect! Thank you so much for your time!