Feeling stuck, the simple yet dreaded for loop has become my nemesis and I could really use some guidance. Currently, I have a Google sheet with 3 rows (excluding headers) and 8 columns. As users input data via a web app, the number of rows will dynamically increase. My goal is to extract this data from the sheet and utilize it to send targeted emails to specific services within our organization.
I only require 6 out of the 8 available columns of data. However, I'm struggling with iterating through all the columns and rows effectively. Despite trying a basic for loop, a nested for loop, and a forEach function, none have yielded the expected output.
Below is my most recent code snippet:
function serviceSelector() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Response');
var row = sheet.getLastRow();
var range = sheet.getDataRange();
var data = range.getValues();
var holderArray = [];
for(i = 0; i < data.length; i++){
holderArray.push(
data[1][0],
data[1][1],
data[1][2],
data[1][4],
data[1][5],
data[1][6]);
}
Logger.log(holderArray);
// switch(holderArray[1]){
// case 'Volunteering':
// MailApp.sendEmail('<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ef8e818b969f8e9a839c9b8a998a819caf88828e8683c18c8082">[email protected]</a>', 'Message ID# ' +holderArray[2], 'You have receive the following message regarding Volunteering: \n\n'+holderArray[3]);
// break;
// case 'Home Help Service':
// MailApp.sendEmail('<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="8beae5eff2fbeafee7f8ffeefdeee5f8cbece6eae2e7a5e8e4e6">[email protected]</a>', 'Message ID# ' +holderArray[2], 'You have receive the following message regarding the Home Help Service: \n\n'+holderArray [3]);
// break;
// }
}
I have commented out the switch statement in order to address the issue related to obtaining the correct data.
Based on the fantastic advice given thus far, I have updated my code as shown below:
function serviceSelector() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Response');
var row = sheet.getLastRow();
var range = sheet.getRange(2,1, sheet.getLastRow() -1,8);
var data = range.getValues();
var holderArray = [];
for(i in data[0]){
for(j in data[i]){
holderArray.push(data[j])
}
}
Logger.log(holderArray);
The resulting log output is displayed below:
[20-03-26 12:35:22:136 GMT] [[Andy, Stevens, [email protected], Wed Mar 25 2020 16:43:38 GMT+0000 (Greenwich Mean Time), k87k4rp4, Home Help Service, Hello World, [email protected]], [...truncated text...]
To see the complete code, including the switch statement where the problem lies, please refer below:
function serviceSelector() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Response');
var row = sheet.getLastRow();
var range = sheet.getRange(2,1, sheet.getLastRow() -1,8);
var data = range.getValues();
var cols = range.getNumColumns();
var rows = range.getNumRows();
var holderArray = [];
for (var i = 0; i < rows; i++){
for (var j = 0; j < cols; j++) {
holderArray.push(data[i][j]);
}
}
Logger.log(holderArray);
Logger.log(holderArray);
switch(holderArray[5]){
case 'Volunteering':
MailApp.sendEmail(holderArray[2], 'Message ID# ' +holderArray[4], 'You have receive the following message regarding Volunteering: \n\n'+holderArray [6]);
break;
case 'Home Help Service':
MailApp.sendEmail(holderArray[2], 'Message ID# ' +holderArray[4], 'You have receive the following message regarding the Home Help Service: \n\n'+holderArray [6]);
break;
}
}