Scripts, scripts, scripts!

I found a service called clicksend last night. It seems to be a good alternative to Twilio.

Take a look :eyes: https://www.clicksend.com/us/pricing/us/#sms

7 Likes

Thanks @SantiagoPerez will take a look, always like seeing a new to play with

4 Likes

@Drearystate and @Darren_Murphy…Being script masters…need some suggestions…

Just managed successfully on my secureSafe app get user on click of a button to permanently erase all 8 folders with the help of a script, and now am able to sync and show progress of folder erasures realtime (you can check the app out…juts add 8 folders…to see some magic) . Instead of combining all the scripts into one…ive kept them separate…Now what I need help with is the following:

All 8 folders are erased on click of a button (step 1)
user profile account including all meta-data erased on click of button in step 2.
It is the button in step 2 which is tied to a timestamp that I want deleted not after 24 hours but 5 minutes…I dont want users to wait 48 hours before creating another account…@Drearystate’s script is perfect for me…which calculation do I need to change to get the timestamp deleted after 5 minutes…Thank you masters!

   function ClearAfter24() {
    var ssActive = SpreadsheetApp.getActiveSpreadsheet();
    SpreadsheetApp.setActiveSheet(getSheetByName('Sheet1'))
    var MyRange = ssActive.getRange("A:A");//selects column A
   var ADayAgo = new Date(date.getTime()-(300*1000));
   for(i = 0; i < MyRange.length; i++) {
   if(MyRange[i].getTime()<ADayAgo){
    getActiveSheet().deleteRow(i);
        }
      }
   }
3 Likes

Wow bro! that was fast! wow! Thank you Sir!

1 Like

The get time was 24*3600 which was the seconds in an hour multiplied by 24 hours so you would just take 60 seconds and multiply it by 5 to get 300 and remove the 24 and multiplier.

1 Like

When Wiz calls…we jump to action sir :slight_smile:

3 Likes

Indebted!

3 Likes

@Drearystate @Wiz.Wazeer - hey guys, have either of you actually tried to execute the above script? As it is, it is severely broken, and there is also one fundamental implementation flaw.

The most serious issue is this:

The above loop starts at the top of the sheet and works its way down, deleting any rows that match the IF condition. The problem with this is that as soon as the first row is deleted, all the remaining index numbers are offset by 1, and so when it hits the next row to be deleted, it will delete the WRONG row.

To avoid this problem, you need to iterate in reverse order - that is, start from the bottom and work your way up.

Other issues:
SpreadsheetApp.setActiveSheet(getSheetByName('Sheet1'))

The above line will throw an error. I’m not quite sure what Joe was trying to do there, but he seems to be trying to chain methods from different classes, which won’t work.

I probably would have done something like:
var sheet = ssActive.getSheetByName('Sheet1');

And then the next line becomes:
var MyRange = sheet.getRange("A:A"); // selects column A
(but see below)

The next issue is in this line:
var ADayAgo = new Date(date.getTime()-(300*1000));

Which throws an error because date is not defined. That should be re-written as:
var ADayAgo = new Date().getTime()-(300*1000);

The next problem is this line:
var MyRange = sheet.getRange("A:A"); //selects column A

That line will return a RANGE object, but not the actual values in the range. The result of this is when you try to iterate through the data further down, there is no data to iterate through, and so the loop doesn’t actually do anything.

So that line above should have been:
var MyRange = sheet.getRange("A:A").getValues(); //selects column A

Other minor issues are around the choice of variable names, which are very misleading. But I guess that’ s just because Joe copy/pasted from another script and didn’t bother changing any of them. I always try and make my function/variable names as descriptive as possible. For my own sanity more than anything else - because I know I’m going to have to come back and debug 3 or 6 months later.
Also, because this is essentially a utility function, and is likely to be re-used, I would try and generalise it as much as possible.

Anyway… all that being said, here is how I would do it. (tested and verified as working)

function delete_rows_older_than(sheetname, mins, col_number) {
    // The function name makes it clear what the purpose is
    // Allowing it to accept parameters makes it flexible
    // sheetname: which sheet to operate on
    // mins: how long before data is considered stale, and can be deleted
    // col_number: which column contains the date/time stamp (A=1, B=2, C=3, etc)
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetname);
    var ms = mins * 60 * 1000; // Convert minutes to milliseconds
    var compare_time = new Date().getTime() - ms;
    
    var last_row = sheet.getLastRow(); // Find our starting point
    if (last_row < 2) { return ;}  // Just in case the sheet is empty

    for (var row = last_row; row > 1; row--) {  // Start from the bottom and work our way up
      var row_time = sheet.getRange(row, col_number).getValue().getTime();
      if (row_time < compare_time) {
        console.log("Deleting Row %s", row);
        sheet.deleteRow(row);
      }
    }
  }

And the above would be called with:

function clear_data() {
    delete_rows_older_than('Sheet1', 5, 1);
  }
7 Likes

Yes, I actively use that script today. All the scripts listed are ones I pulled directly from my sheets. I changed sheet names to generics like Sheet1 or Sheet2 but yeah, I am currently using it.

1 Like

@Darren_Murphy I was trying to get some sleep but now you guys have given me extra work…This is fantastic…Love the way you broke everything down into units and explained it.! I will just use this one…Indebted to you and @Drearystate. :slightly_smiling_face: :slightly_smiling_face: :slightly_smiling_face: :+1: :+1: :+1: :+1:

1 Like

@Drearystate @Darren_Murphy

Hello Guys,

I use available Add-ons in my Google Sheet which gets triggered on Google Form Submit. Is there any possibility if this can be triggered via clicking a button from Glide App or by any other method using Glide App ?

Thanks

1 Like

I’d say the answer is probably yes.
If you can describe your scenario in a bit more detail, I can probably give a more definite answer and perhaps suggest an approach.

1 Like

I am using an Add-on which populates Google Doc Template based on the information we fill up in Google Form. (Addon name is AutoCrat).
What I want is, instead of Autocrat runs on Google Form submit Trigger, I want Autocrat to run on any Action from Glide App as I will be adding details from Form from Glide App instead of Google Form.

I understand this can be achievable using Zapier but I feel, AutoCrat is much more powerful Add-on.

I hope this explains.

1 Like

Okay, so let me know if I have this right…

  • User submits a response via a Google Form
  • This creates a new row in a response sheet in your Google Sheet, which is linked to Glide
  • When the new row is created, an On Form Submit trigger is fired, and this executes a script that “does something” with your add-on

And what you want to do is change the above so that you can use Glide to trigger the sequence instead of a Google Form, yes?

Assuming that’s the case, then all you really need to do is:

  • Configure a Glide component/action/whatever that changes a value somewhere in your spreadsheet
  • Change your On Form Submit trigger to an On Change trigger, and set it up to watch the cell/range where that change will occur.
  • Whenever the change is detected, call the same add-on integration script that you are currently using with the On Form Submit trigger
1 Like

Thank you for the response. Just to be more clear, when new row is added by Google Form submit, the Add-on (which is itself a script, I guess) runs and populates Google doc. This Add-on gives trigger options (On Submit or On Time interval).

I want this script (Add-on) to be run when I add new Row from Glide App.

Correct.

I am sorry I have no idea how to do this. I know little about scripts. Can you help me with it?

The Add-on I am using is third party Add-on script, I feel that I can not have any control over their script to change the trigger run by it (except the option given by them). This is just my assumption.

Kindly help, your help would be appreciated.

1 Like

And Yeah! Happy Birthday @Darren_Murphy

3 Likes

Happy Birthday!

1 Like

Is it the Add On that’s writing the responses to your Google Sheet, or are the responses being sent there by the Google Form?

Without really understanding how this Add On works, it’s a bit difficult to provide advice, but here is something that might work:

  • Configure your Google Form so that it is writing responses to your Google Spreadsheet (if it isn’t already). This should be the same Spreadsheet that your Glide App is connected to.
  • Now, setup a form within Glide that will add a row to that same Response Sheet.

The theory is that the Add On will trigger whenever a new row is added to that sheet. Whether or not that’s the case, I honestly don’t know.

If the above doesn’t work, then you may need to contact the Add On developer for support.

1 Like

Google Form is submitting the responses to Google Sheet.

Add-on starts working from here. It starts working based on the new row and Form Submit trigger.

As far as I know, Add-on is running on Form Submit trigger only and not when new row is added. However, they also give us an option to run on interval of time or Manually.

If I want Addon to run its script on new row added though Glide Form, I only have an option to manually run the Add-on.

I will write to AutoCrat if they can give an option to run when new Row is added. You may also check the link to understand how it works.

Thank you for your help.

1 Like