Scripts, scripts, scripts!

[Richard_Chow]

Thanks Darren.

2:00:03 PM

2:18:49 PM

Error

Exception: Failed to send email: no recipient

Twilio

@ Code.gs:51

This is the error shown.

[Darren_Murphy]

Okay, so that error is pretty obvious.

In the code you posted, you have the following function definintion:

@Richard_Chow

function sendSms(to, body)

But you don’t show how that function is called.
Find the line in your code that calls that function, and check the value of to at that point.

[abe.sherman]

I think this code is a knock out. Check it out.

1. Send a email based on status change.
2. Send email to multiple columns.
3. Send a offline email based on timestamp, After 5 hours in this case. (currently when it goes offline.)

Very very useful code.

Here is a sample spreadsheet you can test and see how it would work
Please change the status in order to see the results.
Please note in the sample sheet. you wont have the email function. Instead click on the link down

Please make a copy of this sheet. And enjoy the magic.

For the Glide 🚀

DDNS customer app

This script will send email if the alert_stats changes stats to either up or down. If it goes down the script will send a email in 5 hours after it has bean down. This is a very useful script. You can use it if a stats has changed. and to send multiple emails that’s listed. Enjoy. [image] function clr() { // Just to clear the store. PropertiesService.getScriptProperties().deleteAllProperties(); } function Alert_when_offline_Online() { const sheet = SpreadsheetApp.getActiveSpreadsheet().ge…

[PabloMFalero]

@gvalero

[adriasoce]

Where do you put the scripts for working?

[Darren_Murphy]

In the GSheet script editor…

Is that what you were asking?

[adriasoce]

You put them there and work on Glide?

[Darren_Murphy]

I’m not sure I understand your question.
Scripts are generally used to extend the capabilities of Glide, or fill in the gaps to do things that Glide cannot do.

For example, there is no current way to execute time-based actions with Glide, so scripts can be used for this. There are many other examples where scripts might be used.

[adriasoce]

oh perfect. Thanks!

[PabloMFalero]

Found this very useful. A script that adds a function to search for coordinates based on an address:

/**

• Returns latitude and longitude values for given address using the Google Maps Geocoder.

• @param {string} address - The address you get the latitude and longitude for.

• @customfunction

*/

function GEOCODE_GOOGLE(address) {

if (address.map) {

    return address.map(GEOCODE_GOOGLE)

} else {

    var r = Maps.newGeocoder().geocode(address)

    for (var i = 0; i < r.results.length; i++) {

        var res = r.results[i]

        return res.geometry.location.lat + ", " + res.geometry.location.lng

    }

}

}

[Uzo]

i found this very…very helpful since i’m doing an App with huge data base.
this code will take all the files from G drive folder and create a G spreadsheet with links and file names of all the files in that folder. Then all you have to do is crate some formula to arrange them the way you need… and is ready to copy and paste to Glide G sheet.

List Google Drive Folder File Names and URLs to a Google Sheet

This Google Apps script produces a new Google sheet containing the list of files in a Google Drive folder, along with their URLs on Google Drive. Such a file is helpful for creating a datalinks manifest for importing datalinks into an ortext.

Steps for installing and running the Google Apps script:

1. Open a new Google Sheet document from the Google account for which you want a Google Drive directory listing.
2. In the toolbar for the new Google Sheet, go to Tools → Script editor
3. Create script for: Script as Web App
4. A code editor will open. Replace any sample code in the editor with the code below.
5. Within the inserted code, replace your-folder with the name of folder for which you want a listing.
6. Save the inserted code; application will ask for a file name.
7. Run by clicking on the right-pointing triangle in the button bar.
8. ‘Authorization required ‘ pop up will appear; grant authorization.
9. A listing of the files in the specified folder, along with URLs for those files, will then appear in your My Drive as a Google Sheet named listing of folder {your-folder}

The Google Apps script code for listing a folder with URLs to a new Google Sheet:

// replace your-folder below with the folder for which you want a listing
function listFolderContents() {
  var foldername = 'your-folder';
  var folderlisting = 'listing of folder ' + foldername;
  
  var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();
  
  var ss = SpreadsheetApp.create(folderlisting);
  var sheet = ss.getActiveSheet();
  sheet.appendRow( ['name', 'link'] );
  
  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );     
  }  
};

I did updated Code BOOK with this code.

[Uzo]

isn’t such a simple but complex code??? lol… and what a time saver!

[Manu.n]

Hello @Uzo
good
it may be a good idea to add an ID of the files.
In my application, I reconstruct the address from an ID to find a file.
example for: webview pdf

.......
sheet.appendRow( ['name', 'link', 'ID'] );
var file;
var name;
var link;
var id;

while(contents.hasNext()) {
  file = contents.next();
  name = file.getName();
  link = file.getUrl();
  id = file.getId();
  sheet.appendRow( [name, link, id] );     
}

[Uzo]

in my case i did not need ID for Glide… but always… more data is more money! lol

[Harry_OBrien]

Nice idea, gives you the ability to embed

[Eric_Penn]

@Drearystate Does anyone know how Google designates who used what amount of app script run time?

Does the runtime quota go against the creator of the workbook or the account who added the trigger?

I ask because I initially setup Glide with a free gmail account. (I’m using a paid account to add the triggers) Should I be looking to migrate my apps to a glide account owned by a paid google workspace account?

The last row “Triggers total runtime” is what I’m concerned about.

[Mark_Turrell]

I have a reasonably active system with a combo of GS and GT (Glide tables).

I have several scripts running to do all kinds of fun things in Google Sheets (the GS bit).

28K executions (they are busy things)

• but they take seconds to run - if we get to 90 mins of run time, then we would have been 1) very very successful and 2) the system would be super slow and no one would use it… which leads to conclusion 3 - this situation could not exist.

It does depend on your use case though

[Eric_Penn]

I am using the same account for multiple workbooks / apps. (Could start using different accounts to set the triggers??)

I’m probably using around 10x the amount of executions as yourself… and I plan/ hope to expand.

[Mark_Turrell]

At a certain point you might thing about whether your app / service is architected in the right way, and whether it is worth the hit to rewrite things.

Personally I do not care about sunk cost. It is what it is. I am also quite happy to trash what I have if I can get something that fits better. It might cost something to redevelop, but if it saves time or creates a better service, then sure.

One thing I recommend is paying for an hour of an expert’s time to walk through your app and your approach. My app changed completely when I worked with @gvalero to go through my app… and he pointed out all the ‘weaknesses’ with Google Sheets that I would hit with my then approach. Basically, he saved my project, as it would have been a total disaster.

And that is just one small example

[gvalero]

Thanks for your note Mark!

It’s a pleasure to help friends.

Feliz día.