Bound app scripts in Googles sheets and asynchronous behavior

[Mike Neirinck]

Hi everyone

I have a simple project in Google sheets. For some friends I am fast putting something together so they can manipulate a Google sheets file through a dialogue screen. 

So I am using the setValues() and getValuest() function. I noticed however these functions are not using callbacks. I went surfing the web and found out using a web form you can add asynchronous behavior. 

I was wondering if I needed to add any asynchronous behavior if I the dialogue screen is in google sheets itself as I am already on the server? 

If necessary how is this done? I guess using promises but I'd like to hear more about this.

Thanks.

[Adam Morris]

Hi there, welcome to the appscripts community.

So there are two runtimes to be aware of: the server-side and client-side. One of them can do async and the other cannot.

The server-side runtime is the runtime that gives you setValues and getValues, and is written in the script editor. So you can make functions that return the data you need, and you can invoke that function from the client-side.

So setValues and getValues wouldn’t need any callback. Your function returns the data you want, and the client should do something with it (and it can do so async, because it’s just the same as the browser).

I’m skipping over details and some options, but hopefully that helps point you to be getter learning and searching for you.

Adam

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e1722f8d-7f8a-4e40-b343-805d045ed69cn%40googlegroups.com.

--

http://classroomtechtools.com

[Mike Neirinck]

Thank you Adam

1 I was diving through the google apps guides and had already found the trigger for Google sheets. Can this trigger be added to a specific sheet or only to the entire spreadsheet project? 

2 Concerning your remarks: I have a spreadsheet with 2 sheets. One contains the input form, the other the values manipulated by the input form. I suppose in this case we are talking solely about server side? It does worry me a bit as I notice a two seconds run time. 

3 The setValues() method returns the range. I suppose I cannot rely the range already was set when it returns? 

Thanks for you answer!

Op dinsdag 9 februari 2021 om 12:15:32 UTC+1 schreef Adam Morris:

[Adam Morris]

Okay let’s get more specific, which trigger?

Are you using custom function?

Can you post any code?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6ec67dcf-66ff-4532-ac5d-e56b8a82f9e8n%40googlegroups.com.

--

http://classroomtechtools.com

[Mike Neirinck]

The returned range is probably just a pointer/reference?

Op dinsdag 9 februari 2021 om 12:30:56 UTC+1 schreef Mike Neirinck:

[Adam Morris]

The returned range of getValues is a 2D array of primitive values. JavaScript doesn’t really have concept of pointers.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a7785a88-c1ca-48a3-bfa2-3456aac2a7b7n%40googlegroups.com.

--

http://classroomtechtools.com

[Mike Neirinck]

I am calling this method here in a Model class. This model class is called by the VC. 

const dataRangeToEdit = this.inventorySheet.getRange(rowId, 1, 1, this.lastColumn);

dataRangeToEdit.setValues(dataOfPlayer);

At this moment I am setting the values. I haven't done anything with the returned range yet because I supposed it was a pointer and hence just references the range object whether it is already changed or not. 

As the setValues method is not a callback method I thought of setting a timer. 

constructor(){

...

this.trigger = ScriptApp.newTrigger("inventoryEdited").forSpreadsheet("XXXXXXXXX").onEdit().create();

}

The XXXXXXXXX however then again seems to be the spreadsheet id and not the id of a particular sheet in the spreadsheet.

I agree that if it the input form were on a html page I could use a promise or so. But that actually would have the same problem.

Op dinsdag 9 februari 2021 om 12:33:27 UTC+1 schreef Mike Neirinck:

[Mike Neirinck]

Range seems to be a class. It is not a reference? 

Op dinsdag 9 februari 2021 om 12:41:55 UTC+1 schreef Mike Neirinck:

[Adam Morris]

A range object is an instance of a Range class, which acts like a reference.

Forget pointers. Primitive values are not references, but objects are like references. It’s a high level scripting language. Whatever applies to JavaScript applies to server side, except async and some other things not usually come across. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e97bdaaa-3e03-4418-90f9-5f435c98293cn%40googlegroups.com.

--

http://classroomtechtools.com

[Mike Neirinck]

Is it my impression or are static properties not possible yet in App script (older version of ECMA script?)

Op dinsdag 9 februari 2021 om 13:15:43 UTC+1 schreef Adam Morris:

[Martin Hawksey]

Hi Mike,

To add to the guidance Adam has provided, you are correct that static properties don't exist in Apps Script. 

To prevent asynchronous collisions Apps Script has the LockService : "This service allows scripts to prevents concurrent access to sections of code. This can be useful when you have multiple users or processes modifying a shared resource and want to prevent collisions."

There is a Stackoverflow Q+A that explains how to use LockService https://stackoverflow.com/q/43223774/1027723 here is also a more detailed example of using LockService with a Google Sheet and web app https://yagisanatode.com/2020/11/25/creating-an-embedded-interactive-chain-story-app-with-google-apps-script-and-google-sheets/ 

Best wishes

Martin

[Mike Neirinck]

The LockService class was what I sought for!

I have another question now :)

Does Google sheets come with a Notification Center? This Notification Center sends out notifications which listeners can pick up. I need it to maintain a design pattern.

Op dinsdag 9 februari 2021 om 15:26:37 UTC+1 schreef m.ha...@gmail.com:

[Martin Hawksey]

You can also Google Drive push notifications https://developers.google.com/drive/api/v3/push#:~:text=a%20resource%20changes.-,Overview,determine%20if%20they%20have%20changed.

In terms of Apps Script an alternative is to use the onEdit/onChange triggers and then use URLFetchApp that can be configured to make http requests

[Adam Morris]

Static properties are okay. Dynamic properties with Symbol... really you can just try it out and if you get syntax error you can assume it’s a bit behind...

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bebfc8dc-911a-46ba-887a-209941cb19f7n%40googlegroups.com.

--

http://classroomtechtools.com

[Mike Neirinck]

This onEdit() is it triggered when it is edited by code or only manually? 

Op dinsdag 9 februari 2021 om 17:49:23 UTC+1 schreef m.ha...@gmail.com:

[Martin Hawksey]

Great question - yes restricted to user interaction and not triggered as a result of script executions and API requests. Restrictions are covered here https://developers.google.com/apps-script/guides/triggers/installable#restrictions

[Mike Neirinck]

Ok, I just figured that out. Is there a way to detect if a cell was changed by code?

Op woensdag 10 februari 2021 om 12:34:15 UTC+1 schreef m.ha...@gmail.com:

[stewart.c...@thepangaia.com]

Hi Nick,

 

I set up the onChange() trigger manually in the start.  Then, when the script starts it deletes the trigger and the last thing it does is to create a new onChange() trigger for the next run.

 

I was exploring using a URLFetch to determine if there were other running processes already and then to terminate.  However, it seemed quite a long winded way of achieving the result so I was interested to know if anyone had a neater solution.

 

I will have a look at the Drive push notification.

 

Thanks,

Stewart

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e1556953-009d-4f46-ae82-e7d6f95fe637n%40googlegroups.com.

[Boris Baublys]

"  Is there a way to detect if a cell was changed by code?  "

https://developers.google.com/apps-script/reference/script/clock-trigger-builder#everyMinutes(Integer)

+

https://developers.google.com/apps-script/reference/properties/properties

=

Get the value from the cell every minute.

Save it in Property and compare with the previous value.

If there is a difference, react.

среда, 10 февраля 2021 г. в 14:36:58 UTC+3, mikene...@gmail.com:

[Mike Neirinck]

Thank you , seems an expensive process though?

Op woensdag 10 februari 2021 om 23:10:16 UTC+1 schreef bbau...@gmail.com:

[Boris Baublys]

Yes of course. Apparently this is a payment for security.

Imagine what it would be like if you could change the value on the sheet with one script, and track this change with another script and start another process.

The minute trigger bypasses this protection to some extent, but it's a whole minute - 60,000 ms :-)

четверг, 11 февраля 2021 г. в 15:12:34 UTC+3, mikene...@gmail.com:

[Adam Morris]

When Mike asked about static properties, I assume he was talking about this:

class Something {

   static get prop () {

       return ‘static property’;

   }

}

Logger.log(Something.prop);  // static property

Which is definitely okay in V8. Or you could use Object.defineProperty:

const Something = {};

Object.defineProperties(Something, {

    “prop”: {

        get () {

            return ‘static property’;

        }

    }

});

Something.prop

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0c66d93e-f9a1-4336-94b7-eb0ee59f011dn%40googlegroups.com.

--

http://classroomtechtools.com