8
\$\begingroup\$

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)
  }
 }
}
\$\endgroup\$
  • \$\begingroup\$ Thanks. I see i missed a couple of semicolons, and it looks like i included a variable that i don't need. I'll fix those issues. Also, I am guessing I can combine two of those loops. Ill be messing around with that next. \$\endgroup\$ – Ognar Oct 18 '15 at 22:52
  • 3
    \$\begingroup\$ Try not to modify the code in your post as it could potentially invalidate incoming/present answers. \$\endgroup\$ – SirPython Oct 18 '15 at 22:59
3
\$\begingroup\$

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);
  }
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.