Scripting in Google Sheets

[AndyCr15]

We use Google Sheets at work, we export a lot of our data into Google Sheets and I was thinking how easily I got stats and analysis on this data if it was actually a database.

This made me wonder, maybe I should learn how to script and such in Google Sheets? Does anyone have any advice? Is it based on another language? Can anyone recommend a decent online course?

Thanks.

 

[Cromulent]

We use Google Sheets at work, we export a lot of our data into Google Sheets and I was thinking how easily I got stats and analysis on this data if it was actually a database.

This made me wonder, maybe I should learn how to script and such in Google Sheets? Does anyone have any advice? Is it based on another language? Can anyone recommend a decent online course?

Thanks.

Google Sheets uses JavaScript for scripting. But have you thought about saving your Google Sheet into CSV format and then importing it into a proper database like PostgreSQL? Alternatively, you could save it into Excel format and work with it from Access or SQL Server.

 

[AndyCr15]

Thanks, I want to be able to do things at work with work documents. I can't even plug in a USB stick and access that, let alone install software or import things into databases

 

[Cromulent]

Thanks, I want to be able to do things at work with work documents. I can't even plug in a USB stick and access that, let alone install software or import things into databases

The other option is to use Google Data Studio which can pull data from Google Sheets. You don't need to install anything to use that.

Disclaimer: I've never used Google Data Studio but I've heard good things about it so it might be worth doing some Googling about it. It seems to be free.

 

[MTA99]

We use Google Sheets at work, we export a lot of our data into Google Sheets and I was thinking how easily I got stats and analysis on this data if it was actually a database.

What kind of analysis would you consider "easy" in a database? I know you know that sum, average etc. are available in Sheets but did you know you can =query() ? Combine with =importrange() for superpowers

Where I think/know there will be a ton of data that might explode a single sheet I use the following pattern:

"Sales - Data" << Raw Data
"Sales - Engine" << Manipulation/Logic
"Sales - Analysis" << Presentation

I'll pass values between the Engine and Analysis (for example a startdate/enddate) to adjust the queries.

This made me wonder, maybe I should learn how to script and such in Google Sheets? Does anyone have any advice? Is it based on another language? Can anyone recommend a decent online course?

As @Cromulent mentioned, the scripts are Javascript with access to the Google API functions. You can build some pretty powerful functionality. This guy is a bit of a Sheets legend - https://www.benlcollins.com/ Use it as inspiration, resist the temptation to copy-paste

The other option is to use Google Data Studio which can pull data from Google Sheets. You don't need to install anything to use that.

Disclaimer: I've never used Google Data Studio but I've heard good things about it so it might be worth doing some Googling about it. It seems to be free.

If the connector is the same one they use for Sheets>BigQuery then the performance is going to be awful

 

[AndyCr15]

Thanks, some good information.

An example of something I was wanting to do -

[Name] - [Employee #] - [Line Manager] - [Most Recent Review Date]

I wanted how many employees each line manager had that had not had a review in the last 6 months. I think I could work that out quite easily as a MySQL query, but wouldn't know how to do it in sheets.

 

[MTA99]

In (pseudo) SQL you'd do something like:

SELECT name, manager, 'time since last review'
WHERE 'time since last review' > 6 months

Obviously the ''time since last review' would need to be calculated using some datetime functions like datediff. Turns out there is a =datedif() in Sheets.

Create a new column, calculate the difference between now() and the last review, then use the =query() to run the same SQL as above. With a sheet you could show all results but order them by date and conditionally format to show late in red, 1 month before next review yellow/orange and everything else green.

The sheets query can be a little temperamental so don't worry if your first attempt fails

 

[AndyCr15]

Well, I'd need to count them grouped by line manager, but yes. Just now having a look at the query function. I didn't realise you could basically use an SQL query in Sheets. (Now looking through the Ben Collins link above.) Thanks.

 

[MTA99]

Oh I see...there are so many overdue reviews that they've asked "Andy the Enforcer!" to find the worst offenders and make sure they never, ever(!!) miss another review?

You might need to create an intermediate table to do the counting using =countif() or =countifs() then query on that

 

[AndyCr15]

Nope, you can use GROUP BY in an SQL command and count how many in that group and you can apparently do it in Google Sheets. I didn't realise you could use Psuedo SQL in Sheets... this will be fun!

Pretty much got it working, bar the date, which it reads from a different cell where the date calculation goes on. I find dates and date formats are always a massive PITA in coding