CODEWITHSUNDEEP
loopsgoogle-apps-scriptgoogle-sheets
Learning_script
Learning_script

Reputation: 91

Very simple question regarding looping in app script

I guess this is very stupid, because it is easy. I tried to find an answer on google, but could not find a solution.

I am doing some basic looping:

function Testing_Loops() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();

  for (var i = 3;i<=lr;i++){

      var Clients = ss.getRange(i,2).getValue();

        }
}

In column 2 I have some rows with clients' names. I want to store these in a variable. My problem is that, at this moment, the only client, I have in the "Clients" variable is the one which is in the last row. This makes sense, because my loop is overwriting my variable each time.

How do I store all the names in on variable? :)

Upvotes: 0

Views: 47

Answers (3)

oshliaer
oshliaer

Reputation: 4979

Use getDataRange() for getting the current values

function Testing_Loops() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var clients = ss.getDataRange().getValues().map(function(row){
    return row[1];
  });

  Logger.log(clients);
}

If you need map it to html for an email try something like this

/**
 *
 */
function getHtmlListForEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var clients = ss
    .getDataRange()
    .getValues()
    .map(function(row) {
      return Utilities.formatString('<li>%s</li>', row[1]);
    })
    .join('\n');

  return Utilities.formatString('<ul>%s</ul>', clients);
}

/**
 *
 */
function sendMail() {
  MailApp.sendEmail('contributor.pw', 'Title', '', {
    htmlBody: getHtmlListForEmail()
  });
}

Upvotes: 1

Learning_script
Learning_script

Reputation: 91

So I managed to store multiple client names in one array. I did it this way:

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var allEmail = ss.getRange(1, 13).getValue();
  var listClientsNames=[];  


  for (var i = 3;i<=lr;i++){

        var originallist = ss.getRange(i,2).getValue();
        listClientsNames.push(originallist);
        }


  //MailApp.sendEmail(allEmail, "Title", listClientsNames);


}

I am now trying to send client list to my email. It is working fine. However, I am receiving the list like this: client1, client2, clent3 and so on.

Can I somehow (in an easy way) do something so it appears vertically: Client 1 Client 2 Client 3

thank you so much for all the help!! I appreciate it alot!

Upvotes: 0

angryip
angryip

Reputation: 2290

Create a list as such:

final List<Client> clients = new ArrayList<>();

Then, loop through and add them:

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();

for (var i = 3;i<=lr;i++){
    clients.add(ss.getRange(i,2).getValue());
}

Then, you can print out what you have:

for (final Client client : clients) {
     System.out.println(client);
}

Upvotes: 0

Related Questions