Scripts, scripts, scripts!

Eric_Penn · January 18, 2021, 10:35pm

The following produces a date with timestamp

var time = new Date();

What could I use to create just a date i.e. 2020/1/18 (Without time stamp) ?

Darren_Murphy · January 18, 2021, 11:53pm

Here you go…

function get_today() {
  var date = new Date();
  var str =
      date.getFullYear() + "/" +
      (date.getMonth() + 1) + "/" +
      date.getDate();
  return str;
}

Called as:

function moo() {
  console.log("Today is %s", get_today());
}

Produces:

7:48:48 AM Notice Execution started
7:48:46 AM Info Today is 2021/1/19
7:48:49 AM Notice Execution completed

PS. Why you should always use yyyy-mm-dd for dates.

Eric_Penn · January 19, 2021, 1:12am

Thank you @Darren_Murphy . You are a gentleman and a scholar.

Darren_Murphy · January 19, 2021, 1:26am

@ehdubya @Wiz.Wazeer - I found a bug in this script. I fixed it, see the edit in my original post.

Wiz.Wazeer · January 19, 2021, 1:28am

Im going to be testing this on the glide countdown timer…I am sure this could help me unlock something (fingers crossed)!

Darren_Murphy · January 19, 2021, 1:34am

Just out of curiosity, what is the problem that you’re hoping this might solve?

Eric_Penn · January 19, 2021, 1:37am

Thank you! I’l give it a go now

Wiz.Wazeer · January 19, 2021, 1:45am

Currently its a combination of numbers and texts. Yours generates numbers only. Glide instantly recognizes that and therefore I can play around with duration cell in the sheet. At present the moment you press pause it all disappears. With yours I want to see if I can keep it in the cell. Then I can truly play around with the countdown timer.

Darren_Murphy · January 19, 2021, 1:54am

Not sure I understand, but okay…
Let me know if I can help in any way.

Wiz.Wazeer · January 19, 2021, 1:56am

Its just me and my weird ways …But I know you will be able to assist…If you can’t then I have no hope lol

Drearystate · January 25, 2021, 3:17pm

@Wiz.Wazeer sorry man, I haven’t been on a ton this past week. What are you trying to do in scripts again?

Wiz.Wazeer · January 25, 2021, 4:14pm

Solved for now! I am working on something …I have managed to create a dynamic calendar using sheet formulas. when x types in 1-12, it will show the corresponding month and all slots for that particular month. I was looking for a script to send bulk emails to several contract workers (cleaning industry)…on selection. Found the script. But if I get stuck I will let you guys know.

Thanks and take it easy on yourself (saw you on CNN with a capitol C, i think–you know what I mean )

abe.sherman · February 23, 2021, 3:39am

Drearystate:

 function onEmailEdit(e) {
     var spreadsheet = SpreadsheetApp.getActive();
     var sheet = SpreadsheetApp.getActiveSheet();
     var range = e.range;
     var cell = range.getA1Notation();
  Logger.log(cell);
    if (cell=='A1' && sheet.getName()=='Sheet1') {
    if (range.getValue()=='Y'||range.getValue()=='y') {
    emailNotifications(sheet);
         }
      }
    }
Here is the second script
function emailNotifications(sheet) {
  Logger.log("Sending emails...");
   var send = sheet.getRange('A1').getValue();
   var subject = sheet.getRange('B1').getValue();
   var text = sheet.getRange('C1').getValue();
   var lastRowEN = sheet.getLastRow();
   var emails = sheet.getRange('D2:D'+lastRowEN).getValues();
     for (i=0; i<emails.length;i++) {
    var email = emails[i][0];
   if (email!="") {
    GmailApp.sendEmail(email, subject, text);
    Logger.log("Email sent");
   }
 }
}
In this script you can see that it first checks to see if A1 has a " Y " as its value. The reason for this is allow the user to simply toggle the ability to send emails or not. When this value is changed to anything else other than " Y " its telling the script to not automatically send emails. The " Y " can be any word or phrase or number I just chose " Y " to be short for the word yes.

The Subject is l

It would be cool if we can send a email based on event, Like reminder a day before then 1 Hour before. Or in the morning everyday at 9:00 If date is today. you get the point.

Example can be, IF(Job Completed) = Never Remind me.
else, If Start is (Today) Then Reminder & set col + 1 ( for first reminder) , then Repeat hour before, So on.
we can even add to script. If End is past. (Was the job Done col) will be checked. If a user unchecked it will reset and resend email. Any thoutes about this?

Darren_Murphy · February 23, 2021, 4:01am

Yes, all possible and fairly straight forward.

Scenario 1: Send an email on a regular schedule (once per day, per hour, etc)
- To do this I will use a time based trigger to setup the schedule
- When the trigger fires, it will call some code that “does stuff”
- If the result of that “stuff” is that an email needs to be sent, then call an Integromat webhook
- Integromat does the rest
Scenario 2: Send an email as a result of an event - ie. when something significant changes
- In this case, an onChange() trigger is appropriate. This ensures that the event is detected as soon as possible after it happens
- The rest is the same as in Scenario 1.

I use both scenarios. Horses for courses

abe.sherman · February 23, 2021, 5:50am

OK need help convert this to

Instead of HardCoded Col, Lets change it to read headers Instead.
Email Send email to 10 Col, (Allow to read 10 Col collect all emails and send it that way. )
Allow 1 header (Col) To trigger upon Edit (For Glide.)
Allow a header (col) If true Continue Script. For user to select if they want to be notified in advance.
Add If start TimeStamp ( Something like we discussed. Above, I will add hourly trigger. )

Col with time stamp, For some reasons also shows GMT-5 Easter Stander time text. Lets avoid that By stripping it?

function sendEmail() { 
var name         = SpreadsheetApp.getActiveSheet().getName();
var row          = SpreadsheetApp.getActiveSheet().getActiveCell().getRow(); // Active Row
var columnactive = SpreadsheetApp.getActiveSheet().getActiveCell().getColumn(); 
var sheetname    = "Quotes"                          // replace sheet name with your sheet name
var sheetid      = "Somthing"         // replace sheet ID with your sheet ID 

if( name == sheetname &&  columnactive == 42  ) { //checks that we're on the correct sheet, replace column number if need it
  var sheet = SpreadsheetApp.openById(sheetid).getSheetByName(sheetname);  
           
 //email notification 
 //getRange(row, column, numRows, numColumns)  // Should work to collect emails? I guess not. 
 var email =  sheet.getRange(row,32,1,10).getValue()     // replace renge parameters if need it
 
 var Title = "Today From Calendar "
 var job_Type  =  sheet.getRange(row,8,1,1).getValue()   
 var The_Job  =  sheet.getRange(row,9,1,1).getValue()  
 var Note  =  sheet.getRange(row,10,1,1).getValue()   
 var Stats  =  sheet.getRange(row,11,1,1).getValue()  
 var Remarks  =  sheet.getRange(row,12,1,1).getValue() 
 var description  =  sheet.getRange(row,2,1,1).getValue()   
 var Client  =  sheet.getRange(row,3,1,1).getValue()   
 var Address  =  sheet.getRange(row,4,1,1).getValue()  
 var Phone  =  sheet.getRange(row,5,1,1).getValue()  
 var Manager_Phone  =  sheet.getRange(row,6,1,1).getValue()  
 var UserEmail  =  sheet.getRange(row,7,1,1).getValue()  
 var TimeStamp  =  sheet.getRange(row,29,1,1).getValue()  
 var Start  =  sheet.getRange(row,30,1,1).getValue()  
 var End  =  sheet.getRange(row,31,1,1).getValue()  

MailApp.sendEmail({
to: email,    
subject: Title, 
body:  "\n\Name: "+Client+
"\n\Phone: "+Phone+     
"\n\Manager: "+Manager_Phone+    
"\n\UserEmail: "+UserEmail+   
"\n\nAddress: "+ Address +  
"\n\Stats: "+Stats+  
"\n\The_Job: "+The_Job+    
"\n\job_Type: "+job_Type+     
"\n\description: "+description+ 
"\n\Note: "+Note+ 
"\n\Remarks: "+Remarks+
"\n\nStart: "+Start+
"\n\End: "+End+    
"\n\nTimeStamp: "+TimeStamp  
});           
}
}

@ThinhDinh @Darren_Murphy

@Darren_Murphy I would take your script that you have and try to work with it first and let you know if i can do it on my own.

Darren_Murphy · February 23, 2021, 6:20am

@abe.sherman this is a classic example of why I don’t use App Scripts to send emails. Having all those hard-coded references to specific columns is way too fragile and subject to breakage. It’s also going to be really slow because of the multiple getRange() calls.

A much better approach (IMO, as I said in my earlier post) is to use a trigger to fire a webhook to Integromat, and then let Integromat do all the heavy lifting for you.

Is there are reason why you don’t want to use something like Integromat or Zapier?

abe.sherman · February 23, 2021, 6:25am

For this particular app i am not paying for yet besides the extra edits. For my other app. I can differently try using it in the future as i am paying for it. The webhook cost money and i dont have it for this app set up, Plus it wont give me everything i want to do with the webhook, Will it?

Even thou its planned in the very long run to pay for it. as of now. i dont have much clients using it besides a handfull. When the app is complete and i can intruduse it. then things will be different.

Darren_Murphy · February 23, 2021, 6:29am

Integromat give you 1000 operations per month for free.

I’ll have a closer look later when I have a bit more free time. In the meantime, can you provide a sheet with some sample data?

abe.sherman · February 23, 2021, 6:32am

Same one we used last time

Antonoff · February 25, 2021, 9:10am

Hi there
I look for an example script: need to compare two tables and if it finds a match, copy the row to the third table.
Now I have it:
function Alarm ()
{
var sheet = SpreadsheetApp.openById (“id”);
var Data1 = sheet.getSheetByName (‘Feed’);
var Data2 = sheet.getSheetByName (‘Alarm-list’);
var Data3 = sheet.getSheetByName (‘Notifications’);
}
Already suggested (thanks @ThinhDinh ) that it should be the expression “When I add to table A, if the input info matches a row in table B, write both to table A and table C, and show an in-app notification. If no, only write to table A.
Same for table B, if the input info matches a row in table A, write both to table B and table C, and show a notification. If no, only write to table B. "
But how to express it in the code - I don’t know.
Help, please.

Topic		Replies	Views
Auto delete row after x amount of time Ask for Help	6	239	July 26, 2023
Erasing a row when date is due Ask for Help	7	267	November 12, 2021
Google sheet script to remove column or row Ask for Help	8	1055	February 11, 2020
Clear cell after set time Ask for Help	12	1283	April 3, 2020
Glide Plugins Project Showcase	8	1036	January 12, 2022

Scripts, scripts, scripts!

Related Topics