Google Apps Script Formatting

Question

Firstly apologies for poor etiquette or dumb questions, this is my first foray into scripting on a google spreadsheet and I must say I'm struggling more than with VBA in excel. Anyway to my problem;

I have a spreadsheet with four columns and fifty three rows. The columns hold; Week Number (eg 33), Week Commencing Date (eg 13/08/12), Hours Worked (eg 31:15:00) and Pay Due (eg $150.00). The first row has the headings and rows 2 to 53 are the weeks of the year. Too easy campese. Now I have managed to write a script that when run will email me a summary email for week. My code is as follows;

  function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary");
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  // Fetch values for each row in the Range.
  var data = dataRange.getValues()
  //Logger.log(data)

  for (i in data) {
    var row = data[i];
    var date = new Date();
    var sheetDate = new Date(row[1]);
    Sdate=Utilities.formatDate(date,'GMT-1000','w')  
    SsheetDate=Utilities.formatDate(sheetDate,'GMT-1000', 'w')
    //Logger.log(Sdate+' =? '+SsheetDate)
    if (Sdate == SsheetDate){
      var emailAddress = row[0];  // First column
      var weeknum = row[0];
      var week = row[1];
      var hours = row[2];
      var pay = row[3];
      var subject = "Timesheet Report for Week " + weeknum;
      MailApp.sendEmail('[email protected]', 
                        subject, 
                        "Chris, \n\nBased on the timesheet data you have submitted last week So and So has recorded the following hours and should be due the calculated pay.\n\n" +
                        "Week Beginning; " + week +
                        "\n\nTotal Hours Worked For The Week; " + hours +
                        "\n\nPay Due For The Week; " + pay);
     //Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
    }    
  }
}

My problem now is I want week beginning to show "Monday 13th August", Total hours worked to show the time and Pay to show as currency. However the script does not retain the formatting from the spreadsheet.

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2012-08-19T05:49:30

There's no way in Apps Script to get the spreadsheet visual formatted cells, only the real values behind them. There's the getNumberFormat method, where you can get any specific formatting the user may have applied to a cell. But this method does not work as desired, since it fails to retrieve the correct formats for a lot of situations, e.g. currencies and percent values, and also does not retrieve the default formats, it's only if the user changed them.

In your case it might be easier just to format your values in the code itself since you're concatenating them manually. To format dates you can use Utilities.formatDate, and for number you probably can work yourself out with plain javascript functions, as num.toFixed() and some string concatenation for your currency symbols.

By the way, I have developed a script that does mail merge and have such formatting capabilities, you may find that it suit you better than this snippet. It's called FormEmailer and is available on the Script Gallery and its site.

Google Apps Script Formatting

1 Answers