Select multiple columns App Script

[Domingo García]

Hello everyone,

I am having issues to finish my code, at the moment is working with: 

function reminder() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), 1);

var data = dataRange.getValues();

  var mensaje = "This is a reminder of things that need to be done:";

for (i in data) {

var row = data[i];

var tarea = row[0];

mensaje = mensaje + "\n" +

"\n"+

  tarea; 

}

var miCorreo = "mye...@gmail.com";

MailApp.sendEmail(miCorreo, "Tasks reminder", mensaje);

}

The output is an email that says:

This is a reminder of things that need to be done:

- Finish curriculum matrix

----

I would like to add "by" and select the values from the 2 column (2 row) of my spreadsheet plus write in bold "This is a reminder of things that need to be done:"

So with look like:

This is a reminder of things that need to be done: (IN BOLD)

- Finish curriculum matrix (by) (column 2 values)

Also adding a user interface so you don't need to enter in the script editor should be fantastic, sorry but i am starting with app script and I am still learning.

Thank you in advance!

[Alan Wells]

In order to use styling in the email, like bold, you must use HTML.

And to use HTML you must use a special setting named htmlBody.

Please study the documentation at the following link:

https://developers.google.com/apps-script/reference/mail/mail-app?hl=en#sendEmail(Object)

The HTML must be "inline".  So you can't use style tags and class names.

The user interface is a different issue, that would take more time to explain.

I haven't addressed that issue here, if someone wants to, please feel free.

I've provide some code that should get you started on the bold line that you want.

function reminder() {
  var list,mailSettings,mensaje,row,tarea;

 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), 1);
  var data = dataRange.getValues();

  var heading = "<b>This is a reminder of things that need to be done:</b><br/>";
 
  list = "";
 
  for (var i in data) {
    row = data[i];
    tarea = row[0];
    
    list += tarea + "<br/>";
  }
 
  mensaje = heading + list;
    
  var miCorreo = "mye...@gmail.com";
 
  mailSettings = {};
 
  mailSettings.to = miCorreo;
  mailSettings.subject = "Tasks reminder";
  mailSettings.htmlBody = mensaje;
    
  MailApp.sendEmail(mailSettings);
}

[Domingo García]

It works,

thank you so much, now I can turn the text bold, how can I add also "by" and the values from the second column?

  var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), 1);

So it appears:

"task column 1" by "who of column 2"

Thank you!!

[Alan Wells]

Probably what you'll want to do is get both columns of values instead of just one.

I made a couple of minor changes that should work:

function reminder() {
  var list,mailSettings,mensaje,row,tarea;
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
 

  var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), 2);//Get two columns

 
  var data = dataRange.getValues();
  var heading = "<b>This is a reminder of things that need to be done:</b><br/>";
 
  list = "";
 
  for (var i in data) {
    row = data[i];
    tarea = row[0];
    

    list += tarea + " by " + row[1] + "<br/>";

[Domingo García]

Thank you!

Now it's working but it has a "by" left over, and shows like this:

task 1 by XX

task 2 by XX

by

How would I add an space between header and mensaje? I am writing [/n] but not working.

Many many thanks!

[Emeric HOCHART]

Maybe  &#x20; 

 

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bdc8880a-5e64-4a64-ae55-192c4e70ca79o%40googlegroups.com.

[Alan Wells]

The "/n" control character doesn't work in HTML.

That's the "new line" command.

In HTML the equivalent of /n is <br/>

So, add another <br/> tag.

var heading = "<b>This is a reminder of things that need to be done:</b><br/><br/>";

Also, add a test in your loop to not process a loop with no data.

function reminder() {
  var list,mailSettings,mensaje,row,tarea;
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
 
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), 2);//Get two columns
 
  var data = dataRange.getValues();

  var heading = "<b>This is a reminder of things that need to be done:</b><br/><br/>";

 
  list = "";
 
  for (var i in data) {
    row = data[i];
    tarea = row[0];
    

    if (!tarea) {
      continue;

    }
    
    list += tarea + " by " + row[1] + "<br/>";
  }
 
  mensaje = heading + list;
    
  var miCorreo = "mye...@gmail.com";
 
  mailSettings = {};
 
  mailSettings.to = miCorreo;
  mailSettings.subject = "Tasks reminder";
  mailSettings.htmlBody = mensaje;
    
  MailApp.sendEmail(mailSettings);
}

[Domingo García]

Thank you so much!

[Alan Wells]

You can add a custom menu to your spreadsheet when it opens, and then run the code from the custom menu instead of opening the script editor.

See:

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=en#addmenuname,-submenus

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Send Reminder Email', functionName: ' reminder'}
  ];
  spreadsheet.addMenu('My Automation', menuItems);
}