The geeky things that keep me happy.
Google Apps Script
Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width just does not budge.
Google Apps Script, Javascript, Jquery, HTML
I was working on a Google Apps Script project lately in Google Sheets that set up parameters in a sidebar and then ran the process once the user clicked the “Submit” button. The problem was that the process was taking a while and that “Submit” Button was ripe to be clicked multiple times by the impatient user, before the server-side code could even finish it’s operation.
To fix this I needed to disable the submit button once it had been clicked and then enable it again once the server-side process was complete. Here, I need to:
I have a very simple Google Sheets Side Bar with a “Submit” and “Cancel” button. When the “Submit” button is clicked it calls the function submittington (can he get any more creative? No. No he can’t).
This function then disables the “Submit” button sends a variable to the client side code.gs and displays it stylishly in the sheet multiple times for the users viewing pleasure. After the code.gs function is executed, it calls back to the client-side submittington function and enables the button.
Behold!!! The example:
Continue reading “Google Apps Script – Disable Enable Submit Button in Sidebar”
Google Apps Script and Google Sheets
Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colors after each category in your sort column is complete.
The Example
I have the following list of numbers in column 1 and 2. I have sorted these numbers by my Grouping Column of planets in column 3. After each grouping, I have alternated the background color to make the transition easier to read.
The Code
Google Apps Script and Google Sheets
Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do something like those sections like put each section of students in their own sheet or set alternating colors for each section to make the sheet easier to read.
Alternatively, you may want to grab sales data by region or sales items by a particular category and work with them in Google Apps Script.
The Example
Let’s say we want to get the range values of the follow sheet by planets. We will be categorizing our data by the Grouping, column C.
First we don’t want to take into account the headers on the first row. Our first grouping will be Mars, followed by Jupiter, Uranus and Mercury. We want to know which column that each category starts on and how many of that category there are.
The Code
Sometimes you have a need to reverse a list quickly in Google Sheets.
That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not in alphabetical or numeric order?
Below are 3 ways to reverse your data:
For the examples below, I’ll be using a list of my favourite Killjoys characters. Yeah, I’m a sci-fi geek.
Continue reading “How do I reverse the Rows in A Column in Google Sheets?”
You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this.
You’ve tried TODAY() and NOW(), but they change dynamically. What you really need here is something that does not change.
Let’s look at two workarounds that can help you out with this problem.
What if you want to get a value or a range from Google Sheets and show it in your sidebar using Google Apps Script?
First you will need to get the value or range by using Googles server-side script. Then you will have to display it client-side in your HTML document.
Documentation on getting the server-side and client-side talking nicely to each other is a little vague. Hopefully, this very basic tutorial will help clear things up.
In this tutorial I will also be using Jquery.
Let’s get started.
A colleague of mine had recently asked me if there was a way to automatically display the day’s date when a cell has data in it without having to use Google Apps Script.
My answer: “Why yes, there is Jim.”
There are two really cool tools that you can use in Google Sheets to get the current date or time and date:
The NOW formula provides a date and time stamp at the time of execution or when the NOW() function is entered into the sheet.
The TODAY formula provides just the date at the time of execution or when the TODAY() formula is entered into the sheet.
That’s all well and good but I want this formula to occur when a user enters something in a cell.
Done:
To achieve this, we can use an IF statement. First, we will state if the cell is empty, then no date should appear. However, if the cell has a value in it, then the date and/or time the value was entered will appear in the corresponding cell.
Here’s the formula:
=IF(A2 ="", "", TODAY()) <<Formula is celll D2
ِAs you can see in the IF statement we are saying; if there is nothing in cell A2 then don’t do anything, otherwise if it contains a value then add today’s date in this cell, D2.
Now, if you want to have this ready for all rows for when a user adds to your data, then drag the formula all the way down to the bottom of the sheet.
Here is an example:
Ready to add some data? Here is what is would look like:
The NOW() and TODAY() functions are Dynamic. This means that they will update automatically. To keep these dates from changing, copy the cell and press <CTRL>+<SHIFT>+<V> or paste special> paste as value.
These two functions are Dynamic and will update constantly. If you are looking to get the date or time of something so that it does not update automatically then check out this tutorial :
You can do exactly the same thing with the NOW formula. Give it a try.
I was trying to rush out some Google Apps Script code to deal with a task on Google Sheets recently. Basically, I had to search through a heap of data and find certain values and do something too them.
My column was reaching across the page to something like Column BK and my rows were over 1000 deep. Running this code was taking forever!!!
My immediate instinct was:
What have I done wrong?
…and my instinct was right.
So after looking at my code again, I discovered that for some reason I go it into my head that I should be searching each cell for the value I needed and then doing something with it.
Sounds logical right? It’s sorta what you are meant to do.
The problem is that I was calling the sever and asking for the range in each cell as I was looping through the entire document. This is super costly and inefficient in terms of time.
Google talks about this in their Google Apps Script Best Practice page under Batch Operations.
Also, if you do run a costly code like this, then you will get a little red light in your Script tool bar that represents your Execution Hints:
Clicking on Execution Hints and expanding the side bar with provide you with a far-too-deserving-polite dressing down about your slow and server costly code.
The Good
So instead of calling the server for each cell I need to get the full range of the data I am working on, search through it client-side to find what cells require modifying and then invoke the modifications.
Let’s look at a simple example:
In this example I want to search through all the results over multiple quizzes and if there is a dash “-” or a zero “0” change the background accordingly.
Yeah! Yeah! I can do this with conditional formatting, but this is an example, damn it, Jim!!!
Below is a sample of the data I will use and here is the link.
Both Good and Bad examples have the same end result. The result should look like below:
First let’s set up the Google Apps Script code file by calling the user interface, sheet and range of data. (Lost? Create your first Apps Script)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
var ui = SpreadsheetApp.getUi(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var rangeData = sheet.getDataRange(); var lastColumn = rangeData.getLastColumn(); var lastRow = rangeData.getLastRow(); var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1); function onOpen() { ui.createMenu('Checker') .addItem('Bad Way', 'badWay') .addItem('Good Way', 'goodWay') .addToUi(); }; |
Hopefully everything is self-explanatory here. We call the spreadsheet first and then look for the active sheet. Inside the active sheet we want the range of the data (rangeData) which will contain all the data in the range. We will use that data to get the last row and column number of the data. Finally we will call the server to get he range we want to work with (searchRange).
Once done, we will create our function, onOpen(). When it is called it will create a menu called Checker with the sub menu Bad Way and Good Way. This isn’t necessary, but it might be easier for you to physically test the difference in the slow method versus the fast (correct) one.
The Bad Way
As mentioned above in the Bad (slow) Way we call the server each time to look at what is in a cell.
|
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/* BAD - Go to each cell and see if it contains a value and then fill in the background if it contains a dash or zero */ function badWay() { //Loop through each column and each row in the sheet. for(i = 1; i < lastColumn; i++){ for (j = 1; j < lastRow ; j++){ var cell = searchRange.getCell(j,i).getValue(); if (cell === "-"){ sheet.getRange(j+1,i+1).setBackground("#cc4125"); }else if (cell === 0){ sheet.getRange(j+1,i+1).setBackground("#e69138"); }; }; }; }; |
As I loop across the columns and then the rows, I am using my search range to get the value of the cell in Line 25. This means I am contacting the server a total for 436 times. This significantly slows things down.
The Good Way
|
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
/* GOOD - Create a client-side array of the relevant data search through the array and if there is a dash or zero, then add the relevant background color. */ function goodWay() { // Get array of values in the search Range var rangeValues = searchRange.getValues(); // Loop through array and if condition met, add relevant // background color. for ( i = 0; i < lastColumn - 1; i++){ for ( j = 0 ; j < lastRow - 1; j++){ if(rangeValues[j][i] === "-"){ sheet.getRange(j+2,i+2).setBackground("#cc4125"); }else if (rangeValues[j][i] === 0){ sheet.getRange(j+2,i+2).setBackground("#e69138"); }; }; }; }; |
In the preferred approach I am taking the array that I created from searchRange.getValues() in Line 36 and searching through it before I make my calls to change the background when a dash or a zero occurs.
Why is this better?
I only make server call to collect the range data once. Then client-side (on my computer in this instance), I do all my searching before calling Google who collects all the changes in a cache until the loops are done before creating background colors all at once.
Super fast.
Speed comparison.
Take a look at the speed differences over ten tests:
The Good Way is the clear winner. You can try it out for yourself if you have been playing along by going to <View><Execution Transcript> in the Script
The take home from this is that, make as little calls to the server as possible. It significantly improves your speed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
var ui = SpreadsheetApp.getUi(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var rangeData = sheet.getDataRange(); var lastColumn = rangeData.getLastColumn(); var lastRow = rangeData.getLastRow(); var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1); function onOpen() { ui.createMenu('Checker') .addItem('Bad Way', 'badWay') .addItem('Good Way', 'goodWay') .addToUi(); }; /* BAD - Go to each cell and see if it contains a value and then fill in the background if it contains a dash or zero */ function badWay() { //Loop through each column and each row in the sheet. for(i = 1; i < lastColumn; i++){ for (j = 1; j < lastRow ; j++){ var cell = searchRange.getCell(j,i).getValue(); if (cell === "-"){ sheet.getRange(j+1,i+1).setBackground("#cc4125"); }else if (cell === 0){ sheet.getRange(j+1,i+1).setBackground("#e69138"); }; }; }; }; /* GOOD - Create a client-side array of the relevant data search through the array and if there is a dash or zero, then add the relevant background color. */ function goodWay() { // Get array of values in the search Range var rangeValues = searchRange.getValues(); // Loop through array and if condition met, add relevant // background color. for ( i = 0; i < lastColumn - 1; i++){ for ( j = 0 ; j < lastRow - 1; j++){ if(rangeValues[j][i] === "-"){ sheet.getRange(j+2,i+2).setBackground("#cc4125"); }else if (rangeValues[j][i] === 0){ sheet.getRange(j+2,i+2).setBackground("#e69138"); }; }; }; }; |
We can achieve a random range of numbers in Google Sheets by using the RANDBETWEEN formula. The formula is quite simple to use. Just add your start range and your end range:
=RANDBETWEEN(start range, end range)
For example, if I want to get a random number between zero(o) and 60 I would do the following:
=RANDBETWEEN(0,60)
The result will provide a whole random number somewhere between these two and including these numbers.
If I were to repeat this formula over a reasonable sample, say 300 times, we should see a fairly even spread of numbers occurring a roughly the same frequency, just like in the graph below:
But what if we wanted to have fewer random numbers in our list at the lower or upper end or on both ends?
Continue reading “Skewed Random Range in Google Sheets (RANDBETWEEN)”