Modifying the Google Spreadsheet script for sending values by email

Question

This is an offshoot of a similar post here. A good answer was provided by Rivero but his code sends the values on the Google Spreadsheet's response sheet. I was wondering if instead the script would return values of the same row and column range specified by the code BUT from other sheet in the same spreadsheet. I'm really new to this and can't seem to find the right combination of codes to do so. Here is Rivero's code, I hope someone can help me accomplish this. Thanks in advance!

/* Send Confirmation Email with Google Forms */
function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  ScriptApp.newTrigger("SendConfirmationMail")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();
}


function SendConfirmationMail(e) {
  try {
    var ss, cc, sendername, subject, columns;
    var header, message, value, textbody, sender, itemID, url;

// This is your email address and you will be in the CC
cc = "name@email.com";

// This will show up as the sender's name
sendername = "name to be displayed as sender";

// Optional but change the following variable
// to have a custom subject for Google Docs emails
subject = "Choose an approppiate subject";

// This is the body of the auto-reply
message = "";

ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

// This is the submitter's email address
sender = e.namedValues["Username"].toString();

// Only include form values that are not blank
for ( var keys in columns ) {
  var key = columns[keys];
//Use this to look for a particular named key
  if ( e.namedValues[key] ) {
    if ( key == "Username" ) {
      header = "The user " + e.namedValues[key] + " has submitted the form, please review the following information.<br />";
    } else {
        message += key + ' ::<br /> '+ e.namedValues[key] + "<br />"; 
      }
    }
  }
}

textbody = header + message;
textbody = textbody.replace("<br>", "\n");

Logger.log("Sending email");
GmailApp.sendEmail(cc, subject, textbody, 
                   {cc: cc, name: sendername, htmlBody: textbody});



} catch (e) {
    Logger.log(e.toString());
  }
}

So... after tinkering around a bit and looking at samples of setting an active sheet within a spreadsheet, I modified the code to replace the ss = Spreadsheet.app.getActiveSheet(); columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0]; to this: ss = SpreadsheetApp.getActiveSpreadsheet(); sh = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); columns = sh.getRange("a1:p2"); The code executes just fine (as seen in the execution transcript) except the column values are not included in the output email. — LeoB, Feb 23 '16 at 06:44
For a complete solution please share a sample of your spreadsheet and the code, with expected behavior and error — Raghvendra Kumar, Feb 23 '16 at 09:56

score 0 · Answer 1 · answered Feb 23 '16 at 09:54

It would better if you can name your sheet and use it instead of using active spreadsheet.

var sheet = SpreadsheetApp.openById('spreadsheetId').getSheetByName('name');

OR you can also use the sheet number to access it.

var sheet = SpreadsheetApp.openById('spreadsheetId').getSheets()[0];

After you have reference to the sheet, get the range for the column followed by data array:

    var columnRange = sheet.getRange('a1Notation');

    var dataArray =  columnRange.getValues();

then iterating the data as per your convenience

for(var i=1; i<=dataArray.length; i++) 
{

}

Modifying the Google Spreadsheet script for sending values by email

1 Answers1