CODEWITHSUNDEEP
javascriptgoogle-apps-script
tom hutn
tom hutn

Reputation: 11

How to Log value of all cells in a column on google app scripts

I am trying to get all the values in column L which is indexed as column 11(var check). However I am only getting the value of the first cell in the column and it stops looping through. I have created a comment for a reference.

Could someone please help me?

Please ignore: It looks like your post is mostly code; please add some more details. It looks like your post is mostly code; please add some more details. It looks like your post is mostly code; please add some more details.

function myFunction(e) {

  var sheet = SpreadsheetApp.openById("1naSWINA8_uxeLUsj0lFntqILyDj2nirb56uvkBel79Y").getSheetByName("CRM Feedback");

  var ss = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");

  var data = sheet.getRange(4, 1, ss.getLastRow(), ss.getLastColumn()).getValues();

  var manager_email = "XXXXX";

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

    var timestamp =             data[i][0];
    var requesterEmail =        data[i][1];
    var starRating =            data[i][2];
    var requestCatergory =      data[i][3];
    var description =           data[i][4];
    var label =                 data[i][5];
    var ticketId =              data[i][6];
    var comment =               data[i][7];
    var status =                data[i][8];
    var priority =              data[i][9];
    var office =                data[i][10];


//I am trying to log all the values in column 11
   var check = data[i][11];
    Logger.log(check)

    var checkTimestamp = data[i][0]

    if(check == false){
      continue;
    } else {

      var subject = "CT IT feedback - ";
      var body = "<body>";
      body += "<br><b>Requester email:</b> " + requesterEmail
      body += "<br><b>Star Rating:</b> " + starRating
      body += "<br><b>Request Category</b> " + requestCatergory
      body += "<br><b>Description:</b> " + description
      body += "<br><b>label: </b> " + label
      body += "<br><b>Ticket ID: </b>" + ticketId
      body += "<br><b>Comment: </b>" + comment
      body += "<br><b>status: </b>" + status
      body += "<br><b>priority:</b> " + priority
      body += "<br><b>office: </b>" + office

      body += "</body>";

      MailApp.sendEmail(manager_email, subject, body, {htmlBody:body}) 

       var sent_string = "sent";

    ss.getRange(i + 1, 12).setValue(sent_string)


    if (sent_string){
      return


     }

    }

  }


}

Upvotes: 0

Views: 820

Answers (1)

ziganotschka
ziganotschka

Reputation: 26826

  • You want to loop through all data rows and send emails for each row where the status is not set to "sent" yet
  • You want to set the status of a row to "sent" after sending an email

The problem is that

  • you have a return statement which makes you exit the for loop and stop iterating

  • You seem not to change the status for the right sheet and the right row

I suggest you to modify your code as following:

function myFunction(e) {

  var sheet = SpreadsheetApp.openById("1naSWINA8_uxeLUsj0lFntqILyDj2nirb56uvkBel79Y").getSheetByName("CRM Feedback");

  var ss = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");

  var data = sheet.getRange(4, 1, ss.getLastRow(), ss.getLastColumn()).getValues();

  var manager_email = "XXX";

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

    var timestamp =             data[i][0];
    var requesterEmail =        data[i][1];
    var starRating =            data[i][2];
    var requestCatergory =      data[i][3];
    var description =           data[i][4];
    var label =                 data[i][5];
    var ticketId =              data[i][6];
    var comment =               data[i][7];
    var status =                data[i][8];
    var priority =              data[i][9];
    var office =                data[i][10];


//I am trying to log all the values in column 11
   var check = data[i][11];
Logger.log(check)

    var checkTimestamp = data[i][0]
    if(check != "sent"){
      var subject = "CT IT feedback - ";
      var body = "<body>";
      body += "<br><b>Requester email:</b> " + requesterEmail
      body += "<br><b>Star Rating:</b> " + starRating
      body += "<br><b>Request Category</b> " + requestCatergory
      body += "<br><b>Description:</b> " + description
      body += "<br><b>label: </b> " + label
      body += "<br><b>Ticket ID: </b>" + ticketId
      body += "<br><b>Comment: </b>" + comment
      body += "<br><b>status: </b>" + status
      body += "<br><b>priority:</b> " + priority
      body += "<br><b>office: </b>" + office

      body += "</body>";

      MailApp.sendEmail(manager_email, subject, body, {htmlBody:body}) 
       var sent_string = "sent";
       //are you sure you want to set the stutus in ss and not in sheet to 'sent'?
      //Keep in mind that you start with row 4
    sheet.getRange(i +4, 12).setValue(sent_string)
    }
  }
}

Upvotes: 1

Related Questions