function onEdit(event) {   var timezone = "GMT-7";   var timestamp_format = "MM-dd-yyyy-hh:mm"; // Timestamp Format.    var updateColName = "Ext Warehouse";   var timeStampColName = "Date/Time Change";   var sheet = event.source.getSheetByName('EXT Changes'); //Name of the sheet where you want to run this script.   var actRng = event.source.getActiveRange();   var editColumn = actRng.getColumn();   var index = actRng.getRowIndex();   var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();   var dateCol = headers[0].indexOf(timeStampColName);   var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;   if (dateCol &gt; -1 &amp;&amp; index &gt; 1 &amp;&amp; editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!     var cell = sheet.getRange(index, dateCol + 1);     var date = Utilities.formatDate(new Date(), timezone, timestamp_format);     cell.setValue(date);   } }   And this one:  function onEdit(event) {   var timezone = "GMT-7";   var timestamp_format = "MM-dd-yyyy-hh:mm"; // Timestamp Format.    var updateColName = "Completed";   var timeStampColName = "Date/Time Completed";   var sheet = event.source.getSheetByName('EXT Changes'); //Name of the sheet where you want to run this script.   var actRng = event.source.getActiveRange();   var editColumn = actRng.getColumn();   var index = actRng.getRowIndex();   var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();   var dateCol = headers[0].indexOf(timeStampColName);   var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;   if (dateCol &gt; -1 &amp;&amp; index &gt; 1 &amp;&amp; editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!     var cell = sheet.getRange(index, dateCol + 1);     var date = Utilities.formatDate(new Date(), timezone, timestamp_format);     cell.setValue(date);   } }
123200
I am trying to run all three of these in any order but only the third runs:  1.  function onEdit(){   var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();   var editedCell = sh.getActiveRange().getColumnIndex();    if(editedCell == 10) {      var range = sh.getRange("A:O");     range.sort([{column: 1}, {column: 14, ascending: true}, {column: 5, ascending: false}]);   } }   2.  function onEdit() {   var sheetName = "PRE-APPRAISAL";   var s = SpreadsheetApp.getActiveSheet();   if (s.getName() !== sheetName) return;   var r = s.getActiveCell();   if( r.getColumn() != 'J' ) { //checks the column     var row = r.getRow();     var time = new Date();     time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm");     SpreadsheetApp.getActiveSheet().getRange('H' + row.toString()).setValue(time);}   }   3.  function onEdit() {   var sheetName = "POST-APPRAISAL";   var s = SpreadsheetApp.getActiveSheet();   if (s.getName() !== sheetName) return;   var r = s.getActiveCell();   if( r.getColumn() != 'J' ) { //checks the column     var row = r.getRow();     var time = new Date();     time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm");     SpreadsheetApp.getActiveSheet().getRange('H' + row.toString()).setValue(time);}   }
26218
I have a spreadsheet in Google Sheets that is shared with five people.  Only a few columns are edited; all of the other columns are locked.  We need the ability to track changes to these editable columns, so, , I added a script in Apps Script like this:  function onEdit() {  if( s.getName() == "MySpecialSheet" ) { //　Set to sheet name    var r = s.getActiveCell();    var col= r.getColumn();    if( col == 3 ) { // Check the column      var nextCell = r.offset(0, 1);       var datetime = new Date();      datetime = Utilities.formatDate(datetime, "GMT+9", "yyyy/MM/dd, hh:mm:ss"); // +9 for Tokyo      nextCell.setValue(datetime);    };  }; }   This automatically inserts the time when the relevant cell was edited, which is great.  However, I also want to add a way to tell who edited the cell.  I checked the  in the apps script documentation, but it seems the only method is getEmail(), which:     is not available in any context that allows a script to run without   that user's authorization, like a simple onOpen(e) or onEdit(e)   trigger, a custom function in Google Sheets, or a web app deployed to   "execute as me" (that is, authorized by the developer instead of the   user).   Since my script uses onEdit(), I have no way to get the email address.  Is there a way to get any kind of ID (I don't need the email address-- it could be the name of the account or a number) for the person editing a cell, and then insert that into another cell?  If not, I suppose I can create a column that people can just type their initials into, but people tend to forget so I would prefer an automated solution if possible.
110293
I am trying to work out what script is required to have multiple timestamps on several tabs however each one I have tried doesn't work for me.  I am new to this. When text is added to column 2 I need a timestamp in column 1.  When text added in 8 I need a timestamp in 7.  When 15 which is a checkbox, a timestamp in 16, and when 18, timestamp in 17.  Is this possible to have so many? My current script is as follows and this is giving me the time stamp in column 1 when column 2 is amended and it doesnt overwrite when it is changed a second time. function onEdit(e) {   var row = e.range.getRow();   var col = e.range.getColumn();   if (col === 2 &amp;&amp; row &gt; 1 &amp;&amp; e.source.getActiveSheet().getName() === &quot;June 2021&quot;) {     if (e.source.getActiveSheet().getRange(row, 1).getValue() == &quot;&quot;) {       e.source.getActiveSheet().getRange(row, 1).setValue(new Date());     }   } }
19010
My  script has now gained quite a following (and it - allegedly - just got bust by upgrading to jQuery 1.7.1). This is a personal request to the team to consider adopting it.   Not that I don't wish to keep on maintaining it, but the next step (allowing users to define one global set of comments to be used across all their sites*) is pretty awkward for me to implement "from the outside", but would be comparatively trivial for the team (I believe).  Goes off to fix script...  * Note: I have a beta half-version of this; it requires   userscripts, and allows you to push/pull between "local" (site-specific) and "global" storage. And even if I get the global sharing 'properly' working, it would still only be for a given machine.
92316
I am working on a formula based on a date/time value to extract the time for the field. This field is based on the Pacific Time Zone and as is appears as  04/04/2016 9:00 PM  I set up this formula below  LEFT(RIGHT(text(  HOC__End_Date_Time__c ),9),7)  However the return result is based on GMT and returns based on a 24-hour clock, is there a way to adjust so it can simply display as 9:00 PM rather than as 24:00.  Hope it helps.
94663
How can i convert a string like this Wed Aug 07 04:30:00 GMT 2013 to a dateTime value?
30685
I have a Timestamp value that comes from my application. The user can be in any given local TimeZone.  Since this date is used for a WebService that assumes the time given is always in GMT, I have a need to convert the user's parameter from say (EST) to (GMT). Here's the kicker: The user is oblivious to his TZ. He enters the creation date that he wants to send to the WS, so what I need is:  User enters: 5/1/2008 6:12 PM (EST)  The parameter to the WS needs to be: 5/1/2008 6:12 PM (GMT)  I know TimeStamps are always supposed to be in GMT by default, but when sending the parameter, even though I created my Calendar from the TS (which is supposed to be in GMT), the hours are always off unless the user is in GMT. What am I missing?  Timestamp issuedDate = (Timestamp) getACPValue(inputs_, "issuedDate"); Calendar issueDate = convertTimestampToJavaCalendar(issuedDate); ... private static java.util.Calendar convertTimestampToJavaCalendar(Timestamp ts_) {   java.util.Calendar cal = java.util.Calendar.getInstance(       GMT_TIMEZONE, EN_US_LOCALE);   cal.setTimeInMillis(ts_.getTime());   return cal; }   With the previous Code, this is what I get as a result (Short Format for easy reading):  [May 1, 2008 11:12 PM]
57923
I wanted to use this, but I don't know if it's actually valid in English:     The specified date is invalid.It points to a non-existing day.   I'm not a native speaker, and I just want to say that the input points to a date that does not exist, like 30th February.
107180
I found a great article on color coding a list item based on dates.  However, this only works for 2 days within submission.  I am trying to have a coding structure that will let me know when something is late.  This is the information I am using:   I have a column that subtracts the two dates, then I have another column that uses this script:   ="&lt;DIV style='font-weight:bold; font-size:24px; color:"&amp;CHOOSE((IF(SubmissionDateDifference&lt;=2,2,1)),"red","green","orange")&amp;";'&gt;•&lt;/DIV&gt;"   With this script, it is only given me the Red and Green colors depending on if the date is greater than 2 days or less than 2 days.  I would like to have it give me Red for 0 days and green for greater than 0.  When I tried to change the numbers to &lt;=0,0, the formula didn't work.  Also, the final piece in case you were wondering is setting up a web part with the list and below the list is a CEWP with this HTML Script:  &lt;script type="text/javascript"&gt; // // Text to HTML // Demo: sharepoint.infoyen.com by Avinash Kumar Dad // var cellTDs = document.getElementsByTagName("TD"); var i=0; var TDContent = " "; while (i &lt; cellTDs.length) { try { TDContent = cellTDs[i].innerText || cellTDs[i].textContent; if ((TDContent.indexOf("&lt;DIV") == 0) &amp;&amp; (TDContent.indexOf("&lt;/DIV&gt;") &gt;= 0)) { cellTDs[i].innerHTML = TDContent; } } catch(err){} i=i+1; } // // ExpGroupRenderData overwrites the default SharePoint function // This part is needed for collapsed groupings // function ExpGroupRenderData(htmlToRender, groupName, isLoaded) {    var tbody=document.getElementById("tbod"+groupName+"_");    var wrapDiv=document.createElement("DIV");    wrapDiv.innerHTML="&lt;TABLE&gt;&lt;TBODY id=\"tbod"+ groupName+"_\" isLoaded=\""+isLoaded+ "\"&gt;"+htmlToRender+"&lt;/TBODY&gt;&lt;/TABLE&gt;";    var tBODYTDs = wrapDiv.getElementsByTagName("TD"); var j=0; var cellTDContent = " ";    while (j &lt; tBODYTDs.length) {       try {          cellTDContent = tBODYTDs[j].innerText || tBODYTDs[j].textContent;          if ((cellTDContent.indexOf("&lt;DIV") == 0) &amp;&amp; (cellTDContent.indexOf("&lt;/DIV&gt;") &gt;= 0)) {            tBODYTDs[j].innerHTML = cellTDContent;          }      }      catch(err){}      j=j+1;   }   tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody); }     The link for this assistance came from    Any assistance will be greatly appreciated.
106040
I've been using Javascript's Date for a project, but noticed today that my code that previously worked is no longer working correctly. Instead of producing Feb as expected, the code below produces March.   My code looks something like this:   current = new Date(); current.setMonth(current.getMonth()+1); //If today is Jan, expect it to be Feb now   This code worked everyday up until today. Is this a Javascript bug or am I going about this the wrong way?
34094
I'm designing a website for a client that has some irregular trading hours. Some days they remain open until 2am, others they close at 10pm. They want these hours displayed on their landing page, so visitors can see what time they open &amp; close at a glance. The trouble is I have to work with an API that returns integers only. Based on these integers and the current time, I can display a currently open or currently closed flag accordingly. As it stands currently, this isn't possible. I can't use custom text anywhere, only integers (24 hour time). I'm having some difficulty working out how to display the time. Currently the client displays their hours like this;  Mon 8am - 2am Tue 8am - 2am Wed 8am - 10pm Thu 8am - 2am Fri 8am - 10pm  However I don't think this is correct, and looks quite confusing (what is 8am - 2am?) shouldn't it be;  Mon 8am - 12am &amp; 12am - 2am Tue 8am - 12am &amp; 12am - 2am Wed 8am - 10pm Thu 8am - 12am &amp; 12am - 2am Fri 8am - 10pm  Admittedly, this also looks quite confusing! Any ideas how I can achieve this in the most user friendly way possible, without using custom text?
32181
I want to schedule my script for last Saturday of every month. I have come up with the below:  45 23 22-28 * *  test $(date +\%u) -eq 6 &amp;&amp; echo "4th Saturday"  &amp;&amp; sh  /folder/script.sh    Is this correct or I need to change it? I can't test it right now as it will be invoked only on last saturday. Please advise.  I have the below for last sunday of every month but i can't understand much of it. The first part gives 24 which is sunday and after -eq gives 7 which i don't know what it means:  00 17  * * 0 [ $(cal -s | tail -2 | awk '{print $1}') -eq $(date | awk '{print $3}') ] &amp;&amp; /folder/script.sh   Can i modify the above to get last saturday?  With Romeo's help I was help to come up with the below answer:  00 17 * * 6 [ $(cal -s | tail -2 | awk '{print $7}') -eq $(date | awk '{print $3}') ] &amp;&amp; /folder/script.sh
101677
I'd really like a user option to show all dates as yyyy-mm-dd HH:mm after the 2 day mark. I find the existing non-standard semi-verbose American Mon DD 'YY style date to be ugly and difficult to parse.  I am not asking for anything complicated here, just a simple user option to show the date and time in the international ISO 8601 format across the board. Since it's already in the tooltip everywhere, it should be a simple change to optionally show it in the page body for those users who want it.  Secondarily, if it's not too difficult, I'd very preferably like to see date &amp; times in my local time, rather than the UTC time as is currently shown used. It seems to make much more sense to me for see a question "asked 2014-02-04 10:19" as being my local time - I don't know what UTC is without using a converter (in this the before-2-days "asked 6 hours ago" is quite good).  (Re-raising this feature-request at the explicit behest of moderator Anna Lear; please don't close as a duplicate).
60087
I'm attempting to set up an email template based on the time of day to begin an email with either a "Good Morning" or a "Good Afternoon" greeting based on the time of day that the email was sent.  I see that the {!Now()} function provides me with the current date and time. However, I'm rather new to Salesforce, and haven't been able to figure out how to capture just the time for a comparison in a conditional statement something like Good {!if {!Now()} &lt; "12:00 AM", "Morning", "Afternoon"}.   I've done quite a bit of searching, and haven't yet been able to find an answer to this question. Any help or reference material would be most appreciated. I've already scoured the Salesforce help pages (including the  page), and browsed the internet for a solution.
73645
I wrote a little bash script that made me stumble across the . I did not know about this problem before and I just dare on posting the --debug output I got from date when my script tried to calculate across this magic date (03:14:07 UTC on 19 January 2038).  date -d "20380119"  date: parsed number part: today/this/now date: input timezone: +01:00 (set from system default) date: warning: using midnight as starting time: 00:00:00 date: starting date/time: '(Y-M-D) 2038-01-19 00:00:00 TZ=+01:00' date: '(Y-M-D) 2038-01-19 00:00:00 TZ=+01:00' = 2147468400 epoch-seconds date: output timezone: +01:00 (set from system default) date: final: 2147468400.000000000 (epoch-seconds) date: final: (Y-M-D) 2038-01-18 23:00:00 (UTC0) date: final: (Y-M-D) 2038-01-19 00:00:00 (output timezone TZ=+01:00) Tue Jan 19 00:00:00 CET 2038   date -d "20380119 + 1 days"  date: parsed hybrid part: +1 day(s) date: input timezone: +01:00 (set from system default) date: warning: using midnight as starting time: 00:00:00 date: starting date/time: '(Y-M-D) 2038-01-19 00:00:00 TZ=+01:00' date: warning: when adding relative days, it is recommended to specify 12:00pm date: error: adding relative date resulted in an invalid date: '(Y-M-D) 2038-01-20 00:00:00 TZ=+01:00' date: invalid date '20380119 + 1 days'   date -d "20380120" --debug  date: parsed number part: today/this/now date: input timezone: +01:00 (set from system default) date: warning: using midnight as starting time: 00:00:00 date: error: invalid date/time value: date:     user provided time: '(Y-M-D) 2038-01-20 00:00:00 TZ=+01:00' date:        normalized time: '(Y-M-D) 2038-01-20 00:00:00 TZ=+01:00' date:  date:      possible reasons: date:        numeric values overflow; date:        missing timezone date: invalid date '20380120'   Is there any way to make GNU date calculate across this date? (on a LINUX 32 bit system)    Operating System: Debian GNU/Linux buster/sid             Kernel: Linux 4.12.0-2-686-pae       Architecture: x86
15108
We need to accomplish the following:  On the 1st day of each quarter, we would like to update all open opportunities that have "close date" in the past quarter and set the new "close date" to be the last day of this new quarter.  I'm new to SFDC, but from my research it seems triggers or Time-dependent workflow may not do this. Can we write and schedule a cron script to do this?  Any help will be greatly appreciated! Thanks!
67443
In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students. The Template sheet contains protected cells and the attendance is marked by entering Student's ID number in the space given (unprotected cells). I use the following script to duplicate multiple sheets and rename them everyday:  function createDailyAttendance() {   var ss = SpreadsheetApp.getActiveSpreadsheet();   var refss = ss.getSheetByName("DataPointers");      // Get the range Row and Column information.   var dataRangeRow = refss.getRange("K2").getValue();   //var dataRangeCol = ss.getRangeByName(ColName).getValue();      // Get the range of cells that store Duplicate sheet name.   var AttendanceDataRange = refss.getRange(dataRangeRow);    var AttendanceObjects = AttendanceDataRange.getValues();    var template = ss.getSheetByName('Template');    for (var i=0; i &lt; AttendanceObjects.length; i++) {       // Put the sheet you want to create in a variable      var sheet = ss.getSheetByName(AttendanceObjects[i]);        // Check if the sheet you want to create already exists. If so,       // log this and loop back. If not, create the new sheet.         if (sheet) {            Logger.log("Sheet " + AttendanceObjects[i] + "already exists");         } else {            template.copyTo(ss).setName(AttendanceObjects[i]);            }         }   return; }   This script helps me create multiple copies of sheets from Template but the duplicate copies do not retain the Cell/Range permissions. Is there a way to add a loop function which extracts permission from Template and applies it every time the loop template.copyTo creates a sheet?