-1

I've made a script, but I need the numbers off 3 rows to be on percentage and I also want to use only 2 decimals and format the date (one of my table colums) as "yyyy-MM-dd".

this is my actual script:

function getdata() 
{

  var ss = SpreadsheetApp.openByUrl("url");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("name");
  var dataRange = sheet.getRange(10,4,1,9);
  var colors = dataRange.getBackgrounds();
  

// Auxiliar

  var sp = ss.getSheetByName("sheet1");
  var pont = sp.getRange("cell").getValue();
  Logger.log(pont)

  // Se é o dia D
  if(pont==true)
  {
  var tab = ss.getSheetByName("sheet");
  var datavalues = tab.getDataRange().getValues();
  Logger.log(datavalues)

  // Improve later
  
  var html = '<font size=0.5>(This is an automatic message.  If you should not be receiving it, contact the ownwer)</font> \
  <br><br>\
  🚩 <b>HEADER</b> 🚩 \
  <br><br>\
  Body <font size=1.0>(DB disponible <a href="link">here</a>)</font> \
  <br><br> ' + '<table>';

  if(datavalues.length>0)
  {
      for(var i=0; i<datavalues.length;i++) // counting the rows of the table
        {
        html+= '<tr style = bgcolor="Blue">'; // html = html + something
        for(var j = 0; j<datavalues[i].length;j++)
        // check if the row is header of the table or normal row
          {
            if(i==0)
              {
                html+= Utilities.formatString('<td bgcolor="LightBlue"; \
                style = "\
                border:0px solid black; \
                border-collapse: collapse; \
                text-align:center; \
                font-family:questrial; \
                font-size:100% \
                " <th>%s</th></td>', datavalues[i][j]);          
                
            } else
                {
                  html+=Utilities.formatString('<td \
                  style = "border:1px solid black; \
                  border-collapse: collapse; \
                  border-style:none none dashed none; \
                  text-align:center; \
                  font-family:questrial; \
                  font-size:90% \
                  " > %s</td>', datavalues[i][j]);
                } 
          }
    }
  html+='<table>'
  }
  return html;
  }
}

function sendEmail()
{
var ss = SpreadsheetApp.openByUrl("link");

// ponteiro

  var sp = ss.getSheetByName("sheet1");
  var pont = sp.getRange("cell").getValue();
  Logger.log(pont)

  // Se é o dia D'
  // , emails 

  if(pont==true)
  {
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "Test",
    htmlBody: getdata()
    })
  }
}

1 Answer 1

0

To get the data as text strings in the format the data shows in the spreadsheet, replace this line:

  var datavalues = tab.getDataRange().getValues();

...with this:

  var datavalues = tab.getDataRange().getDisplayValues();

Then format the data in the spreadsheet the way you want it to appear.

To get numbers with exactly two decimals, use Format > Number > Number.

To get dates in the ISO8601 standard date format yyyy-MM-dd, use Format > Number > Custom date and time > 1930-08-05.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.