Google App Script Spreadsheet

0
votes

here is the code 

function makeaCVdoc(){


var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var startRow = 2;
  var numRows = sheet.getLastRow() -1;
  var EMAIL_SENT = "EMAIL_SENT";
  var subject = "Here is your CV";

  //var dataRange = sheet.getRange(2,2,numRows,50);

  var data = dataRange.getValues();

  for(var i = 0; i < data.length; ++i)
  {

var row = data[i];

var firstname = row[1];
var secondname = row[2];
var thiredname = row[3];
var address = row[4]
var email_address = row[5];
var homenumber = row[6];
var mobilenumber= row[7];
var objective = row[8];
var language = row[9];
var educationwithdegree = row[10];
var computerskill = row[11];
var TrainingCourse = row[12];
var Hobbies = row[13];
var DOB = row[14];
var nationality = row[15];
var MaritalStatus = row[16]
var emailSent = row[17];

var CVdoc = DocumentApp.create(firstname+' '+secondname+' '+thiredname);
var par1 = CVdoc.getBody().appendParagraph(firstname+' '+secondname+' '+thiredname);
par1.setHeading(DocumentApp.ParagraphHeading.TITLE);
var par2 = CVdoc.getBody().appendParagraph("Address: ");
par2.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(address);
var par3 = CVdoc.getBody().appendParagraph("Email Address: ");
par3.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(email_address);
var par4 = CVdoc.getBody().appendParagraph("Home Number: ");
par4.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(homenumber);
var par5 = CVdoc.getBody().appendParagraph("Mobile Number: ");
par5.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(mobilenumber);
var par6 = CVdoc.getBody().appendParagraph("Objective: ");
par6.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(objective);
var par7 = CVdoc.getBody().appendParagraph("Spoken Languages: ");
par7.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(language);
var par8 = CVdoc.getBody().appendParagraph("Education and Degree: ");
par8.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(educationwithdegree);
var par9 = CVdoc.getBody().appendParagraph("Computer Skills: ");
par9.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(computerskill);
var par10 = CVdoc.getBody().appendParagraph("Training Courses: ");
par10.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(TrainingCourse);
var par11 = CVdoc.getBody().appendParagraph("Hobbies: ");
par11.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(Hobbies);
var par12 = CVdoc.getBody().appendParagraph("Nationality: ");
par12.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(nationality);
var par13 = CVdoc.getBody().appendParagraph("Date Of Birth: ");
par13.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(DOB);
var par14 = CVdoc.getBody().appendParagraph("Marital Status: ");
par14.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(MaritalStatus);
var url = CVdoc.getUrl();
var body = 'Link to your doc: ' + url +'Thank you for using our Tech';
if(emailSent != EMAIL_SENT)
{
  //GmailApp.sendEmail(email_address, subject, body);
  //sheet.getRange(2,17).setValue(EMAIL_SENT);
  SpreadsheetApp.flush();
}


 } 
}

it's basically a form that i want to use to get all the information that it's needed in order to make a CV and after the user enter all the information and submit the form it's stored at a spreadsheet than i have this code inside this spreadsheet but something wrong with the comment lines and i still don't know why

i get something wrong with the getRange() command which lead to another code error at the GmailApp command right now i made them as comments but no matter what i do i don't know what's wrong so please could anyone help me

some said that they needed more information about the error like i said the error is at the commented lines 

  var dataRange = sheet.getRange(2,2,numRows,50);
  GmailApp.sendEmail(email_address, subject, body);
  sheet.getRange(2,17).setValue(EMAIL_SENT);

it basically tell me error at line whatever the line is and write the line to me as a function what i mean is getRange(number,number,number,number);

and that's all what i get nothing more nothing less

this is the code after i changed it

function makeaCVdoc() 
{

var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var startRow = 2;
  var numRows = sheet.getLastRow() -1;
  var EMAIL_SENT = "EMAIL_SENT";
  var subject = "Here is your CV";

  var dataRange = sheet.getRange("B2:R2");

  var data = dataRange.getValues();

  for(var i = 0; i < data.length; ++i)
  {

var row = data[i];

var firstname = row[0];
var secondname = row[1];
var thiredname = row[2];
var address = row[3]
var email_address = row[4];
var homenumber = row[5];
var mobilenumber= row[6];
var objective = row[7];
var language = row[8];
var educationwithdegree = row[9];
var computerskill = row[10];
var TrainingCourse = row[11];
var Hobbies = row[12];
var DOB = row[13];
var nationality = row[14];
var MaritalStatus = row[15]
var emailSent = row[16];

var CVdoc = DocumentApp.create(firstname+' '+secondname+' '+thiredname);
var par1 = CVdoc.getBody().appendParagraph(firstname+' '+secondname+' '+thiredname);
par1.setHeading(DocumentApp.ParagraphHeading.TITLE);
var par2 = CVdoc.getBody().appendParagraph("Address: ");
par2.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(address);
var par3 = CVdoc.getBody().appendParagraph("Email Address: ");
par3.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(email_address);
var par4 = CVdoc.getBody().appendParagraph("Home Number: ");
par4.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(homenumber);
var par5 = CVdoc.getBody().appendParagraph("Mobile Number: ");
par5.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(mobilenumber);
var par6 = CVdoc.getBody().appendParagraph("Objective: ");
par6.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(objective);
var par7 = CVdoc.getBody().appendParagraph("Spoken Languages: ");
par7.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(language);
var par8 = CVdoc.getBody().appendParagraph("Education and Degree: ");
par8.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(educationwithdegree);
var par9 = CVdoc.getBody().appendParagraph("Computer Skills: ");
par9.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(computerskill);
var par10 = CVdoc.getBody().appendParagraph("Training Courses: ");
par10.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(TrainingCourse);
var par11 = CVdoc.getBody().appendParagraph("Hobbies: ");
par11.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(Hobbies);
var par12 = CVdoc.getBody().appendParagraph("Nationality: ");
par12.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(nationality);
var par13 = CVdoc.getBody().appendParagraph("Date Of Birth: ");
par13.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(DOB);
var par14 = CVdoc.getBody().appendParagraph("Marital Status: ");
par14.setHeading(DocumentApp.ParagraphHeading.HEADING2);
CVdoc.getBody().appendParagraph(MaritalStatus);
CVdoc.saveAndClose();
var CVdocID = CVdoc.getId();
var url = CVdoc.getUrl();
var pdf = DocsList.getFileById(CVdocID).getAs("application/pdf");
var body = 'Here is your CV ' + pdf +'Thank you for using our Tech';
if(emailSent != EMAIL_SENT)
{
  GmailApp.sendEmail(email_address, subject, body);
  sheet.getRange("r2").setValue(EMAIL_SENT);
  SpreadsheetApp.flush();


  }
  } 
  sheet.deleteRow(2);
}
1
javascriptgoogle-apps-scriptgoogle-sheets
It will be great if you can post example spreadsheet with code (read-only will be great). On what line do you receive the error, what error? It will be great if give more information about the error you receive. – Vasim
This has nothing to do with Google App Engine.. – Lipis

1 Answers

1
votes

I'm not sure how to fix what you have going on, I suspect it is structure and the fact that you're pulling 50 columns in your getRange, but really only need 17 of them (possible column width error?) Here is a script I use to procedurally generate a document from a form submission. In my case, I delete the document when I'm finished, but you'd leave it there and collect the URL and enclose it in the email you spit out. You can also use chained functions to repeat some of the repetitive tasks like copyBody.replaceText("text", variable), but if I gave you my version of that, it is more confusing since I use a lot of for() iterators, and the data in that example comes from UiApp not a form and spreadsheet combo.

  function onFormSubmit(e) { // add an onsubmit trigger
  var sheet = SpreadsheetApp.getActiveSheet();
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();
  //Set Unique ID for each entry
  sheet.getRange(row,2).setValue(row);

  // Full name and email address values come from the spreadsheet form
  var email_address = "[email protected]";//used as a reporting email for errors
  var userName = e.values[1];
  var date     = e.values[2];
  var vendor = e.values[3];
  var coordinator = e.values[4];
  var buyer = e.values[5];
  var category = e.values[7];
  var submittedBy = e.values[79];


  //Values from form
  var line1 = e.values[9];
  var item1 = e.values[10];
  var quantity1 = e.values[11];
  var sku1 = e.values[12];
  var price1 = e.values[13];
  var total1 = e.values[14];

  var line2 = e.values[16];
  var item2 = e.values[17];
  var quantity2 = e.values[18];
  var sku2 = e.values[19];
  var price2 = e.values[20];
  var total2 = e.values[21];

  var line3 = e.values[23];
  var item3 = e.values[24];
  var quantity3 = e.values[25];
  var sku3 = e.values[26];
  var price3 = e.values[27];
  var total3 = e.values[28];

  var line4 = e.values[30];
  var item4 = e.values[31];
  var quantity4 = e.values[32];
  var sku4 = e.values[33];
  var price4 = e.values[34];
  var total4 = e.values[35];

  var line5 = e.values[37];
  var item5 = e.values[38];
  var quantity5 = e.values[39];
  var sku5 = e.values[40];
  var price5 = e.values[41];
  var total5 = e.values[42];

  var line6 = e.values[44];
  var item6 = e.values[45];
  var quantity6 = e.values[46];
  var sku6 = e.values[47];
  var price6 = e.values[48];
  var total6 = e.values[49];

  var line7 = e.values[51];
  var item7 = e.values[52];
  var quantity7 = e.values[53];
  var sku7 = e.values[54];
  var price7 = e.values[55];
  var total7 = e.values[56];

  var line8 = e.values[58];
  var item8 = e.values[59];
  var quantity8 = e.values[60];
  var sku8 = e.values[61];
  var price8 = e.values[62];
  var total8 = e.values[63];

  var line9 = e.values[65];
  var item9 = e.values[66];
  var quantity9 = e.values[67];
  var sku9 = e.values[68];
  var price9 = e.values[69];
  var total9 = e.values[70];

  var line10 = e.values[72];
  var item10 = e.values[73];
  var quantity10 = e.values[74];
  var sku10 = e.values[75];
  var price10 = e.values[76];
  var total10 = e.values[77];

  var sumRange = sheet.getRange(1,86,sheet.getLastRow(),1);
  sumRange.setNumberFormat("0,000,000.00");

  sheet.getRange(row,86,1,1).setNumberFormat("0,000,000.00");
  var sum = Math.round(100*(sheet.getRange(row,86,1,1).getValue())/100);
  Logger.log(sum);
  sheet.getRange(row,86,1,1).setNumberFormat("0,000,000.00");
  Logger.log(sum);


  //Document variables
  var docTemplate = "Doc Id for your template document to replace text";  // *** replace with your template ID ***
  var todaysDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");
  var docName     = "CV Document name " +userName +" on " +todaysDate;

  // Get document template, copy it as a new temp doc, and save the Doc’s id
  var copyId = DocsList.getFileById(docTemplate)
  .makeCopy(docName)
  .getId();
  // Open the temporary document
  var copyDoc = DocumentApp.openById(copyId);
  // Get the document’s body section
  var copyBody = copyDoc.getActiveSection();

  // Replace place holder keys this can be iterated with a number of for() loops
  // Template Header
  copyBody.replaceText('keyDate', date);   
  copyBody.replaceText('keyVendor', vendor);
  copyBody.replaceText('keyCoordinator', coordinator);
  copyBody.replaceText('keyBuyer', buyer);
  copyBody.replaceText('keyCategory', category);

  //Template Table
  copyBody.replaceText('keyLine1', line1);   
  copyBody.replaceText('keyItem1', item1);
  copyBody.replaceText('keyQuantity1', quantity1);
  copyBody.replaceText('keySKU1', sku1);
  copyBody.replaceText('keyPrice1', price1);
  copyBody.replaceText('keyTotal1', total1);

  copyBody.replaceText('keyLine1', line1);   
  copyBody.replaceText('keyItem1', item1);
  copyBody.replaceText('keyQuantity1', quantity1);
  copyBody.replaceText('keySKU1', sku1);
  copyBody.replaceText('keyPrice1', price1);
  copyBody.replaceText('keyTotal1', total1);

  copyBody.replaceText('keyLine2', line2);   
  copyBody.replaceText('keyItem2', item2);
  copyBody.replaceText('keyQuantity2', quantity2);
  copyBody.replaceText('keySKU2', sku2);
  copyBody.replaceText('keyPrice2', price2);
  copyBody.replaceText('keyTotal2', total2);

  copyBody.replaceText('keyLine3', line3);   
  copyBody.replaceText('keyItem3', item3);
  copyBody.replaceText('keyQuantity3', quantity3);
  copyBody.replaceText('keySKU3', sku3);
  copyBody.replaceText('keyPrice3', price3);
  copyBody.replaceText('keyTotal3', total3);

  copyBody.replaceText('keyLine4', line4);   
  copyBody.replaceText('keyItem4', item4);
  copyBody.replaceText('keyQuantity4', quantity4);
  copyBody.replaceText('keySKU4', sku4);
  copyBody.replaceText('keyPrice4', price4);
  copyBody.replaceText('keyTotal4', total4);

  copyBody.replaceText('keyLine5', line5);   
  copyBody.replaceText('keyItem5', item5);
  copyBody.replaceText('keyQuantity5', quantity5);
  copyBody.replaceText('keySKU5', sku5);
  copyBody.replaceText('keyPrice5', price5);
  copyBody.replaceText('keyTotal5', total5);

  copyBody.replaceText('keyLine6', line6);   
  copyBody.replaceText('keyItem6', item6);
  copyBody.replaceText('keyQuantity6', quantity6);
  copyBody.replaceText('keySKU6', sku6);
  copyBody.replaceText('keyPrice6', price6);
  copyBody.replaceText('keyTotal6', total6);

  copyBody.replaceText('keyLine7', line7);   
  copyBody.replaceText('keyItem7', item7);
  copyBody.replaceText('keyQuantity7', quantity7);
  copyBody.replaceText('keySKU7', sku7);
  copyBody.replaceText('keyPrice7', price7);
  copyBody.replaceText('keyTotal7', total7);

  copyBody.replaceText('keyLine8', line8);   
  copyBody.replaceText('keyItem8', item8);
  copyBody.replaceText('keyQuantity8', quantity8);
  copyBody.replaceText('keySKU8', sku8);
  copyBody.replaceText('keyPrice8', price8);
  copyBody.replaceText('keyTotal8', total8);

  copyBody.replaceText('keyLine9', line9);   
  copyBody.replaceText('keyItem9', item9);
  copyBody.replaceText('keyQuantity9', quantity9);
  copyBody.replaceText('keySKU9', sku9);
  copyBody.replaceText('keyPrice9', price9);
  copyBody.replaceText('keyTotal9', total9);

  copyBody.replaceText('keyLineA', line10);   
  copyBody.replaceText('keyItemA', item10);
  copyBody.replaceText('keyQuantityA', quantity10);
  copyBody.replaceText('keySKUA', sku10);
  copyBody.replaceText('keyPriceA', price10);
  copyBody.replaceText('keyTotalA', total10);

  copyBody.replaceText('keySum', +sum);

  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf"); 
  // Attach PDF and send the email
  var subject = "CV submitted by "+submittedBy;
  var body    = userName +" has submitted a new CV, which is attached to this email.\nPlease ensure there are no errors before printing.\nIf there are errors, please notify: "+email_address +"\n\n";
  MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf}); 
  // Delete temp file
  DocsList.getFileById(copyId).setTrashed(true);//remove this if you added URL above for posterity
}

If you're interested I can post the iterator version of this code that makes loops for repetitive actions. Just let me know. It's a lot more complex than this example because there are a lot of function calls, but if you're comfortable with that, I'll put it up.

I know for a fact that this code will make an invoice type/shipping label document because my template is built that way. I did not change the code to your variables and needs. You will also need to build a template document with keys to replace with each user's entry values, but that isn't terribly hard. All of the formatting can be done there, and you can add headings and such in the script that read from submitted data columns so if the section (such as nationality) is left blank there's no heading or content for it.

You're looking for technique #3 in this tutorial http://googleappsdeveloper.blogspot.com/2011/10/4-ways-to-do-mail-merge-using-google.html for a guide on how to do the script I provided.

Also, be careful with body.replaceText() using keys that are numbers. When you get to 10, it will replace the text with keyText1 and add a 0 string to the end. I'd recommend using alphabet letters or words instead for your keyValues.

Here is the iterated function version of the same basic process. Ignore the UiApp lines and remove the app methods, and it will work like a form. I'm including it as an alternative to show how you can use for loops to construct a document from a template without having to hand-code it all. It's also more modular, so if I need to change something, I can just add or modify that function and not mess with the rest of it.

function doPost(e){
  var app = UiApp.getActiveApplication();
  var vertPanel = app.createVerticalPanel();
  var grantName = e.parameter.grantName;
  var userEmail = Session.getActiveUser().getEmail();
  var mrNumber = e.parameter.MR;
  var ss = SpreadsheetApp.openById("Id for the form response spreadsheet");
  var infoSheet = ss.getSheetByName('name of the form response sheet');
  var keyRow = selectKeysByGrant(grantName);
  var keyHeaders = infoSheet.getRange(1,1,1,infoSheet.getLastColumn()).getValues();
  var infoData = infoSheet.getRange(keyRow,1,1,infoSheet.getLastColumn()).getValues();
  var keyIds = new Array (makeKeys(keyHeaders));
  var dataVars = new Array (makeDataVars(keyIds));
  var copyId = assignKeys(dataVars,keyIds,infoData,userEmail,mrNumber);
  var pdf = mailCheatSheet(copyId,userEmail,mrNumber);
  var completeLabel = app.createLabel('You should receive your worksheet results in your email soon.');
  app.add(completeLabel);
  return app;

  }

//Select Data Row from funding type
function selectKeysByGrant(grantName){
  var grant = null;
  switch (grantName){
    case "1":
      grant = 2
      break;
    case "2":
      grant = 3
      break;
    case "3":
      grant = 4
      break;
    case "4":
      grant = 5
      break;
  }
  return grant;
}

function makeKeys(keyHeaders){
  var keys = [];
  for (var i = 0; i< keyHeaders[0].length; i++){
    keys.push("key"+keyHeaders[0][i]);
  }
  return keys;
}

function makeDataVars(keyIds){
  var dataVarLabels = [];
  for (var k = 0; k < keyIds[0].length; k++){
    dataVarLabels.push(keyIds[0][k] +"text");
  }
  return dataVarLabels;
}


function assignKeys(dataVars,keyIds,infoData,userEmail,mrNumber){
  var variables = dataVars;
  var keys = keyIds;
  var rowData = infoData;
  var userEmail = userEmail;
  var date = Utilities.formatDate(new Date, "CST","MM/dd/yyyy");
  var mrNum = mrNumber;
  Logger.log(variables);
  Logger.log(keys);
  Logger.log(rowData);

  var docTemplate = "ID of your document template";
  var todaysDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");
  var docName     = "New name of document created submitted by " +userEmail +" on " +todaysDate;

  // Get document template, copy it as a new temp doc, and save the Doc’s id
  var copyId = DocsList.getFileById(docTemplate).makeCopy(docName).getId();
  // Open the temporary document
  var copyDoc = DocumentApp.openById(copyId);
  // Get the document’s body section
  var copyBody = copyDoc.getActiveSection();

   for (n = 1; n < variables[0].length; n++){
     copyBody.replaceText(keys[0][n].toString(),rowData[0][n].toString())
   }
  //replacing some text from the UIapp but isn't on the spreadsheet
  copyBody.replaceText('keyUserEmail',userEmail);
  copyBody.replaceText('keyDate',date);
  copyBody.replaceText('keyMR',mrNum);


  // Save and close the temporary document
  copyDoc.saveAndClose();
  return copyId;

}

function mailCheatSheet(copyId,userEmail,mrNumber){
  var copyId = copyId;
  var userEmail = userEmail;
  var mrNum = mrNumber;
  // Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf"); 
  // Attach PDF and send the email
  var subject = "SA Funding request by: "+userEmail;
  var body    = userEmail +" has submitted a document for " +mrNumber +", which is attached to this email.\nPlease ensure there are no errors before printing.\nIf there are errors, please notify: [email protected].\n\n";
  MailApp.sendEmail(userEmail, subject, body, {name: 'CV Helperbot', htmlBody: body, attachments: pdf}); 
  // Delete temp file
  DocsList.getFileById(copyId).setTrashed(true);
  return pdf;
}