HiveBrain v1.2.0
Get Started
← Back to all entries
patternjavascriptMinor

Google Sheet with a column of names merged with Google Doc

Submitted by: @import:stackexchange-codereview··
0
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.

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.