Databases
StackOverflow2013
Postsdbo
POGoogle Apps Script Formatting
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGoogle Apps Script Formatting
    text
    copied!<p>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;</p> <p>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;</p> <pre><code> 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('me@myemail.com', 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) } } } </code></pre> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload