How to create a new google doc for every iteration?

timohuennebeck 0
Viewed 28

I've just created a google script that pulls the data from a google sheet and imports it into specific spots that have been declared in a google doc, i.e. the column "name" has a value of "Melanie Perkins" and replaces the value "name" inside the google doc with that name.

But, when I run the script it just pulls the data from the first row (company, address, name) and then creates the google doc. However, I have multiple rows and want to create a new google doc for each row but can't seem to get it to work. That's the code and screenshots of the google doc and sheet that I've used.

Google Sheets

Google Docs

function docsAutomation() {
  let date = new Date().toLocaleDateString();
  
  // opens spreadsheet
  let ss = SpreadsheetApp.openById('1Eu4iiINGM6ytxfLdEznRmwq__qxTfuaJF6cNW5LVOYQ');

  // duplicates the file
  let documentId = DriveApp.getFileById('1IlzS3CWQsDjxMw8b6TvgdoeJqOOb8HZpH7B8kl5ZmaE').makeCopy().getId();
  
  // renames the copied file
  DriveApp.getFileById(documentId).setName('timo_huennebeck_' + date);  
  
  let fullDocument = DocumentApp.openById(documentId).getBody();
  let range = ss.getRange("A2:C4");
  fullDocument.replaceText('##personalName##', 'Timo');

  for (let i = 1; i < 4; i++) {

    cell = range.getCell(i, 1);
    business = cell.getValue(); 
    fullDocument.replaceText('##business' + i + '##', business);

    cell = range.getCell(i, 2);
    address = cell.getValue();
    fullDocument.replaceText('##address' + i + '##', address);

    cell = range.getCell(i, 3);
    name = cell.getValue();
    fullDocument.replaceText('##name' + i + '##', name);
  }
}
google-apps-script javascript
1 Answers

A new doc for each row.

You are also missing a field so I assumed it is next column

function docsAutomation() {
  const date = new Date().toLocaleDateString();
  const ss = SpreadsheetApp.openById('1Eu4iiINGM6ytxfLdEznRmwq__qxTfuaJF6cNW5LVOYQ');
  const sh = ss.getSheets()[0];
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getDisplayValues();
  vs.forEach((r, i) => {
    let id = DriveApp.getFileById('1IlzS3CWQsDjxMw8b6TvgdoeJqOOb8HZpH7B8kl5ZmaE').makeCopy().getId();
    DriveApp.getFileById(id).setName('timo_huennebeck_' + date);
    let body = DocumentApp.openById(id).getBody();
    body.replaceText(`##business##`, r[0]);
    body.replaceText('##address##', r[1]);
    body.replaceText('##name##', r[2]);
    body.replaceText('##personalName##', r[3]);//do not see any data for this
  });
}
user30hu0
Related