patternjavascriptMinor
Google Sheet with a column of names merged with Google Doc
Viewed 0 times
googlecolumnwithsheetdocnamesmerged
Problem
I wrote my first script for google apps, but I am just learning how this all works. The script works fine, but I am sure there are things that are not done properly. Any advice is appreciated.
The code creates awards from a Google Sheet where a column of names (last name, first name) is separated and text is replaced on a doc template accordingly.
The code creates awards from a Google Sheet where a column of names (last name, first name) is separated and text is replaced on a doc template accordingly.
function createAward() {
var myDoc ='ID';
var mySheet ='ID';
var mySourceFolder ='ID';
var myNewFolder ='ID';
var date = Utilities.formatDate(new Date(), "GMT", "MM-dd-yyyy"); // "yyyy-MM- dd'T'HH:mm:ss'Z'"
//Data
var ss = SpreadsheetApp.openById(mySheet);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
var range = sheet.getRange("A:A");
var colNum = 1; //just 1 col
var rowNum = 2; //start at 2 because there is a header
var numRows = 1; //process data 1 row at a time
//Splitting names
for (rowNum = 2; rowNum <= lastRow; rowNum ++){
var cell = range.getCell(rowNum,colNum).getValues()[0];
var sCell = cell[0].split(",");
sheet.getRange(rowNum,colNum +1,1,sCell.length).setValues([sCell]);
}
//Loop to make doc for each student
for (rowNum = 2; rowNum <= lastRow; rowNum ++){
var dataRange = sheet.getRange(rowNum, 1, numRows, 3)
var data = dataRange.getValues();
// Data loop --
for (i in data) {
var row = data[i];
// columns to use for variables
var fName = row[2];
var lName = row[1];
// make copy of doc and save as student name.
var docTemplate = DriveApp.getFileById(myDoc);
var newFile = docTemplate.makeCopy(fName + lName);
var newDoc = DocumentApp.openById(newFile.getId());
DriveApp.getFolderById(myNewFolder).addFile(newFile);
DriveApp.getFolderById(mySourceFolder).removeFile(newFile);
// replace text
var doc = DocumentApp.getActiveDocument();
var body = newDoc.getBody();
body.replaceText('{first name}', fName);
body.replaceText('{last name}', lName);
body.replaceText('{date}' , date)
}
}
}Solution
I realized I didn't need all those loops so I simplified things to 1 loop:
for (rowNum = 2; rowNum <= lastRow; rowNum ++){
//Splitting names
var cell = range.getCell(rowNum,colNum).getValues()[0];
var sCell = cell[0].split(", ");
sheet.getRange(rowNum,colNum +1,1,sCell.length).setValues([sCell]);
var fName = sCell[1]; // first name
var lName = sCell[0]; // last name
// make copy of doc and save as student name.
var docTemplate = DriveApp.getFileById(myDoc);
var newFile = docTemplate.makeCopy(fName + lName);
var newDoc = DocumentApp.openById(newFile.getId());
DriveApp.getFolderById(myNewFolder).addFile(newFile);
DriveApp.getFolderById(mySourceFolder).removeFile(newFile);
// replace text
var body = newDoc.getBody();
body.replaceText('{first name}', fName);
body.replaceText('{last name}', lName);
body.replaceText('{date}' , date);
}Code Snippets
for (rowNum = 2; rowNum <= lastRow; rowNum ++){
//Splitting names
var cell = range.getCell(rowNum,colNum).getValues()[0];
var sCell = cell[0].split(", ");
sheet.getRange(rowNum,colNum +1,1,sCell.length).setValues([sCell]);
var fName = sCell[1]; // first name
var lName = sCell[0]; // last name
// make copy of doc and save as student name.
var docTemplate = DriveApp.getFileById(myDoc);
var newFile = docTemplate.makeCopy(fName + lName);
var newDoc = DocumentApp.openById(newFile.getId());
DriveApp.getFolderById(myNewFolder).addFile(newFile);
DriveApp.getFolderById(mySourceFolder).removeFile(newFile);
// replace text
var body = newDoc.getBody();
body.replaceText('{first name}', fName);
body.replaceText('{last name}', lName);
body.replaceText('{date}' , date);
}Context
StackExchange Code Review Q#107978, answer score: 3
Revisions (0)
No revisions yet.