Creating a variable to be inserted in to the body of an email

Viewed 40

This is my first post here and I am very new to this so please bear with me!

I have a Google sheet that receives Form responses to log maintenance jobs. Form response is the trigger for a script which generates a "Job Number" and emails it back to the person who submitted the form.

I have been asked to include the text from the "Describe the Issue" field of the form along with the job number in the email response.

The original script was not created by me it came from Here

I tried to add a variable called "Description" using the following:

var Description = sheet.getRange(LastRow, 6).getValue();

I then entered the variable in to the body of the email like this:

var Message = 'Your job with the description ' + Description + ' has been logged. \n \nIf you need to send more photos, please reply to this email with the photos attached. \n \nYour job number is: ' + (LastAutoNumber+1) + ' \n \nThank you!';
// Send the mail
MailApp.sendEmail(EmailAddress, Subject, Message);}

The email never arrived. I tried putting the code for the new variable between lines 48-49, that did not work. I then tried between lines 6-7, that did not work either. Is there a specific place I should be creating this variable?

I am not really sure why this is failing, any help would be greatly appreciated!

Thanks,

Chris

The working script is here.

function addAutoNumber() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Form responses 1");
// Get the last row that has content.
var LastRow = sheet.getLastRow();
// Set the first Auto Number
var AutoNumberStart=100;  

//---- First run ------------

//Check if the first column is Timestamp
if (sheet.getRange(1, 1).getValue() == "Timestamp") {
// insert a column to the left and the text "Auto Number" in the first row
sheet.insertColumnBefore(1);
sheet.getRange(1, 1).setValue("Job Number");
// Fix for (probably) a bug that formats the new column 
// with the same format of the column used to insert it,
// in this case the column gets the date format like the Timestamp column.
// So we set the format to number
sheet.getRange("A2:A").setNumberFormat(0); 
// check if there are already form responses and add numbers for them
if (LastRow>1) {
  for(var ii=2; ii <= LastRow; ii++) {
    sheet.getRange(ii, 1).setValue(AutoNumberStart);
    AutoNumberStart++;
  }
}
}

// ---- Add new Auto Number ----------

// Check if there is a Number in the AutoNumber column 
// for the last Form Submission
if (sheet.getRange(LastRow, 1).isBlank()) {
// Check if it is the first Form submission and set the AutoNumberStart
if (LastRow == 2) {
  sheet.getRange(LastRow, 1).setValue(AutoNumberStart);
} else {
  // Get the Last AutoNumber from the previous row
  var LastAutoNumber = sheet.getRange(LastRow-1, 1).getValue();
  // Set the next AutoNumber
  sheet.getRange(LastRow, 1).setValue(LastAutoNumber+1);
}
// ---- Send a response receipt with the AutoNumber as a reference number
// Get the email from the column D (4-th column)
// Change the number "4" to the number of the mail column in your sheet
var EmailAddress = sheet.getRange(LastRow, 3).getValue();
// Set the email subject
var Subject = "Your Job Number";
//Create the message body
// The "/n" in the message body means a new line
var Message = 'Your job has been logged. \n \nIf you need to send more photos, please reply to 
this email with the photos attached. \n \nYour job number is: ' + (LastAutoNumber+1) + ' \n 
\nThank you!';
// Send the mail
MailApp.sendEmail(EmailAddress, Subject, Message);}

}
1 Answers

Well I tried starting again using exactly the same method and it worked!

Maybe a character was wrong the first time, who knows.

Related