Good Explanation of using an "Array Function" in Google Sheets

[Uncle Gizmo]

This is a good explanation of using an "Array Function" in Google sheets, particularly Google Apps Script. The simplest method of addressing data in a spreadsheet is to do it line by line. However this hogs resources and can take a lot longer. Harnessing an array function which extracts all of the values from the sheet in one go, then operates on them in memory and puts the answers back into the sheet. A little bit more complicated to get your head around, but much faster and uses far less resources.

A convenient way for MS Access programmers to think about Google sheets and Google Apps Script:- Think of the Google sheet as a form in datasheet view, think about Google app scripts as the code behind the form, the VBA code, but in a Google App script, you use JavaScript code. Microsoft is gradually moving some of its products over to using JavaScript code, so it is well worth getting a handle on it.

5 Google Sheets Script Functions You Need to Know

Google Sheets is a powerful cloud-based spreadsheet tool that lets you do nearly everything you could do in Microsoft Excel. But the real power of Google Sheets is the Google Scripting feature that comes with it. Google Apps scripting is a background scripting tool that works not only in Google...

helpdeskgeek.com

 

[Isaac]

Thanks for sharing these, Uncle Gizmo.
Is it still the case that any Sheets that has scripting in it requires the user to "Allow with their Google account" when they first enter it? I think that was the case last time I looked at utilizing Sheets with scripting more, and it kind of turned me off of it.

 

[Uncle Gizmo]

s it still the case that any Sheets that has scripting in it requires the user to "Allow with their Google account"

I'm not well read up on this, so take what I say with a pinch of salt. I think you need permissions for most scripts that run within in a document like a spreadsheet or text.

The permissions offers you a level protection because you can set it up so that only people with a Google account can run your scripts. If you've got a business account, then just people within your business. Some scripts can run public, anyone can use them.

It appears to be quite a well protected system, and indeed I think several government organisations around the world use the Google system, hence I'm relatively sure that you are well protected because these large organisations have rigorous vetting procedures.

 

[Uncle Gizmo]

requires the user to "Allow with their Google account"

Having just imported some JSON files into my Google drive I ran into the problem of where I needed to authorise an app to do anything with these files.

It occurred to me that was actually what you meant. To use certain third-party applications you need to authorise them and give them access to your data, if that's what you meant, then I totally agree with you.

That is a very big failing with Google as far as I'm concerned. You have no idea who has made this apps, you have to rely on Google Vetting them for you, and indeed in their terms they don't take any responsibility for this, it's at your own risk.

This is totally wrong for any business to allow. The only sensible approach for the business is to disable authorisation of these apps, which I think you can do...

So really, until Google gets its finger out and makes a set of applications which are its own design and handle all of the different scenarios that a business might need, or at least have a decent system for vetting the apps provided by third parties, then it's really a no-go for any serious business.

 

[Isaac]

Yeah the message can appear daunting to some