Fill Google Doc with Sheets data

Using body.replaceText() to replace specific areas of a Google Doc with data from a Sheet. Three parts:
1. Replace data directly on a Doc
2. Make a copy of the Doc (template) then replace data on the copy
3. Loop through several rows of Sheet data, make a copy of the Doc (template) for each, then replace data on the copy

Important notes:
* Make sure the IDs for the Doc and the Folder are correct to your environment.

Sheet (to copy from File then “Make a copy”)
https://docs.google.com/spreadsheets/d/1E_MRGBMpvflH9SAOylJ6s53O29rEsuVlnF8oPzzy9xs/edit

Doc (to copy from File then “Make a copy”)
https://docs.google.com/document/d/1js6PIpBM9QBjR4o5rtDFq9_twSv7RPIQ1h0j5osHgTE/edit

Scripts:
function first(){
const body = DocumentApp.openById(“1js6PIpBM9QBjR4o5rtDFq9_twSv7RPIQ1h0j5osHgTE”).getBody();
const sheet = SpreadsheetApp.getActive().getActiveSheet();

let data = sheet.getRange(2,1,1,7).getValues();
let replacements = [“//PROJECT NAME//”,”//DATE//”,”//NAME//”,”//COMPANY//”,”//PROPOSAL//”,”//LOCATION//”,”//AMOUNT//”];

for (let i in replacements){
body.replaceText(replacements[i],data[0][i]);
}
}

function second(){
const sheet = SpreadsheetApp.getActive().getActiveSheet();
let data = sheet.getRange(2,1,1,7).getValues();

const file = DriveApp.getFileById(“1js6PIpBM9QBjR4o5rtDFq9_twSv7RPIQ1h0j5osHgTE”).makeCopy(“Project Proposal: “+data[0][0],DriveApp.getFolderById(“1osc-Ho5UCCjda6PiRtYtvcYvc3SDF_5X”));
const body = DocumentApp.openById(file.getId()).getBody();

let replacements = [“//PROJECT NAME//”,”//DATE//”,”//NAME//”,”//COMPANY//”,”//PROPOSAL//”,”//LOCATION//”,”//AMOUNT//”];

for (let i in replacements){
body.replaceText(replacements[i],data[0][i]);
}
}

function third(){
const sheet = SpreadsheetApp.getActive().getActiveSheet();
let data = sheet.getRange(2,1,4,7).getValues();

let body;
let replacements = [“//PROJECT NAME//”,”//DATE//”,”//NAME//”,”//COMPANY//”,”//PROPOSAL//”,”//LOCATION//”,”//AMOUNT//”];

for (let i in data){
body = DocumentApp.openById(DriveApp.getFileById(“1js6PIpBM9QBjR4o5rtDFq9_twSv7RPIQ1h0j5osHgTE”).makeCopy(“Project Proposal: “+data[i][0],DriveApp.getFolderById(“1osc-Ho5UCCjda6PiRtYtvcYvc3SDF_5X”)).getId());
for (let j in replacements)
body.replaceText(replacements[j],data[i][j]);
}
}

Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305

Leave a Reply

Your email address will not be published. Required fields are marked *