JavaScript the spreadsheet for fun & profit

September 8, 2017

Intro

Hi! Today you’re gonna learn about how to use JavaScript within Google Sheets, and have a blast doing it.

Wait why would I wanna do that in the first place, you say?

Well let’s say you’re a coder, and you have to do some spreadsheet work, and you’ve never really done any of it before, and you can’t wrap your head around all those convoluted functions spreadsheets expect you to know… then there you go. Don’t. Just use JavaScript instead.

The usual boilerplate: This ain’t supposed to be no paragon of virtue, shining beacon of clean, efficient code. It’s not even supposed to be the right way. It’s just a way that worked for me.

And before I forget, just a heads up: you’re gonna have some fun here.

Contents

Backstory

Feel free to skip this.

You’ve been warned.

Back in my old job we had small team for a task that was basically the most repetitive and laborious exercise anyone in humanity has ever come up with.

I had an NDA so I can’t go into detail, but I’d basically spend hours and hours and hours manually tweaking very long and cryptic strings of data, and typing in endless numbers, dates, and random gibberish.

I was getting paid by the hour, so at first it was okay, but I eventually ran out of Netflix to watch. And that’s when my noggin started to connive…

At first I solved that problem by coming up with a monstrosity that would later be known by such endearing names as Regex Blackhole®, Regex Rollercoaster®, and The Scariest Spreadsheet You’ve Ever Seen®.

Here’s an example: You would paste into a cell the whole contents of a PDF we would get from another company. Then it would parse it, find the bits we wanted, take into account the availability of my teammates, and which tasks had to be done which day, and the different phases the tasks had to be done in, and, finally, it would spit out the team schedule for the whole month, all perfectly formatted for our system, everything crunched and organized automagically.

Brilliant.

Except, of course, whenever any one tiny bit of anything in there had to change.

To this day I’m haunted by the memories of being forced, time and time again, to re-submerge into that regexiferian maelstrom of mindbending lunacy.

Regexiferianre·ge·xi·fe·ri·an /ˈreɡeksəfərēən/adj.1. in an unrecoverably insanity-inducing manner2. relating to or denoting said horrors

All the tools I wrote were under my username there. And when I quit, all data related to that user was deleted.

Oh well.

Tears in the rain.

But now my former team is back to the stone age. You’ve heard of the stone age, right?

Stone Age
noun
1 → a period in the history of humankind in which people rubbed sticks together to make fire, threw spears around for hunting, and drew naughty shit in cave walls because porn was hard to come by.

Really though, as much as I’d like to claim this article is for them, that’d only be partially true.

The main reason is, I’ve realised the other day that I’ve done enough front-end work by now to know it’s not what I wanna be doing long-term, and this is my farewell to front-end-y, JavaScript-y things.

Now I was never one known to say goodbye and walk away quietly into the darkness, so here we are.

Eureka! Those numbers are the exact times when our lolcats will be attacked! Look!

Clara at 11h45, and then in military time Magdalena at 5h20 in the afternoon, and Amadeus at midnight.

Okay.

That’s it.

That’s the last straw.

We need to call our space drones for a pre-emptive strike.

We know the exact time and location where whoever wants our lolcats will be, so let’s bomb the living daylights out of it!

We’ll need the exact address, and the exact hour for the strike, formatted precisely in the e-format our drones require.

Spies have been dispatched to inform the lolcats not to be at those locations at the time of the strike.

To the drones!

S.D.S.C.U.I.F.P.

That’s short for Space Drone Strike Control Universal Interface Format Protocol, of course.

We’ll need a Sheets file with the following fields side by side, one line per ordnance unit.

COUNTRY, REGION, CITY, STREET, SPOT, HOUR, MINUTE, ADJUSTMENT

So that’s one spot per lolcat and, since these drones are terribly inaccurate, we’ll need some extra strikes around the area to be sure. Let’s say, for each location we want an extra punch a bit to the left, another a bit to the right, then another a bit north, another a bit south, one extra to the left AND southwards, then left and north, then… whoa, that’s a big list.

That’s because functions in Sheets aren’t allowed to mess with any random cell in the spreadsheet just like that. It’s the law. See it yourself. To save you the trouble:

A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells. To edit arbitrary cells, use a custom menu to run a function instead.

So yeah yeah, custom menus. You know what? Custom menus are boring. You can follow the instructions if you want. Me?

I WANT A BIG, RED “DO NOT PRESS” BUTTON!

Should be easy enough. First off, Insert > Drawing...:

Then poke around ‘til you end up with something like…

Save & Close, then click the three dots at the top right corner of our new awesome button, and let’s give it a function.

Of course, now that we assigned that function to the button, we need to have a function by that name in our code. Let’s try this:

That’s normal. What’s happening here is you need to authorize the script to make changes to the spreadsheet, as opposed to the default of having the spreadsheet perform changes on itself.

It’ll tell you the app hasn’t been verified by Google, and that that’s scary and the spreadsheet might sell your kids in the black market. Come on now. We’re the ones writing the code here.

Hi Ellen
“JavaScript the spreadsheet for fun & profit” wants to
View and manage your spreadsheets in Google Drive
Allow JavaScript the spreadsheet for fun & profit to do this?

For crying out loud, Google! Come on!! Lolcats are in danger!!!1

So did it work?

Yip. Cell I33 now does have the value we wanted it to have.

So now we can just use that and the rest is straight forward code, right?

Maybe. No spoilers.

So off we go. To recapitulate, the fields we want are: COUNTRY, REGION, CITY, STREET, SPOT, HOUR, MINUTE, and ADJUSTMENT. Those are mostly fixed, the lolcats do live in the same funny street (Ha-Ha). We’ll need SPOT, HOUR, and MINUTE to be specific to each cat. And then ADJUSTMENT will have a ton of different values—bit to the left, bit to the right, etc.

So let’s put all of that in arrays just so it’s organized. We’ll end up with something like:

Now let’s toss in a Logger.log(allTheAdjustments) before the last curly brace, and CTRL + Enter to see if it worked:

Perfect.

And for our grand finale:

functionSDSCUIFP(){// ↓ ↓// All that crap from the snippet above goes here// ↑ ↑// Loop through every lolcatfor(p=0;p<lolcats.length;p++){// Loop through every adjustmentfor(q=0;q<allTheAdjustments.length;q++){// And then, for every possibility, get:// - Values 0-3 of the fields array// - All values for the current lolcat// - The current adjustment we're looping throughvarcolumn=1varrow=getNextRow()for(r=0;r<=3;r++){setValue(letters[column]+row,fields[r])column++}for(s=0;s<lolcats[p].length;s++){setValue(letters[column]+row,lolcats[p][s])column++}setValue(letters[column]+row,allTheAdjustments[q])}}}

Look how pretty it is in action:

Wait a minute, that’s rather slow. And see how it snagged there at the beginning?

We have a lot of possibilities to go through. All the adjustments, all the cats…

Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow.
Consequently, if you plan to use a custom function multiple times on a large range of data, consider modifying the function so that it accepts a range as input in the form of a two-dimensional array, then returns a two-dimensional array that can overflow into the appropriate cells.

So instead of all those hundreds of setValue() calls, what we need is to put everything into an array, and then call setValue() only once, feeding it the whole array.

We also need a new function that calls SpreadsheetApp.getActiveSpreadsheet().getRange().setValues() instead of SpreadsheetApp.getActiveSpreadsheet().getRange().setValue(). Notice that it’s values, plural, now.

And lastly, when we feed the cell range and the big ass array into setValues(), both arguments have to be the exact same height and width. That is, the array has to fit the range we’re saying it fits in. (Hence the maxim: if it fits, I sits.)

So, right, gimme a minute…

Hrm.

How about this?

functionsetValues(cellRange,value){SpreadsheetApp.getActiveSpreadsheet().getRange(cellRange).setValues(value)}functionSDSCUIFP(){// ↓ ↓// A million for loops, not shown for brevity's sake// ↑ ↑// This is our new arrayvarleGrandeArray=[]// Same ol' same ol'for(p=0;p<lolcats.length;p++){for(q=0;q<allTheAdjustments.length;q++){varcolumn=1// thisIsOneRow is new (and self-explanatory)varthisIsOneRow=[]for(r=0;r<=3;r++){thisIsOneRow.push([fields[r]])column++}for(s=0;s<lolcats[p].length;s++){thisIsOneRow.push([lolcats[p][s]])column++}thisIsOneRow.push([allTheAdjustments[q]])// Above we pushed all the values for // this one row into `thisIsOneRow`// and now we push that as a sub-array // into the big 'un.leGrandeArray.push(thisIsOneRow)}}varrow=getNextRow()// The format for a range is something like// "A11:D25", a "spreadsheet square" if you will.// For starters, letter "A" and whatever the // next free row issetValues(letters[1]+row+":"// Then the last letter, which depends on the// width of our array+letters[fields.length]// And here the array length, not forgetting// that we started at getNextRow(), not at the// first row of the spreadsheet+((leGrandeArray.length-1)+row)// And below goes whatever we'll be sticking into// the range we just defined above,leGrandeArray)}

Yes, yes. A lot of $5 words there, but will it work?

Hooray! If I hadn’t skipped the chapter about Big-O the other day I could almost make myself sound smart at this point, eh?

So that’s it. Our space drones will get their orders and whoever’s trying to snatch our lolcats will soon be vaporized, along with a decent chunk of Brittany.

Happy ending. JavaScript saved the lolcats!

Or did it?

I didn’t hear a boom. Did you? I was expecting a boom. Hm…

Telemetry indicates our space drones never left the station, and the evidence points to one Bobby Tables as the main suspect for sabotaging our operation.

But…

The lolcats are still safe. I don’t understand. How did they get away?

Let’s see…

Intelligence in the area reports witnesses saw a colorful flash, too quick to catch but a glimpse, and that whoever this hero was, they have left behind an encrypted message while protecting the lolcats.

Who could it be? The Mysterious Stranger? Captain Obvious? The Silver Shroud?

Computer, analyze!

Let’s see. The secret code is pages and pages and pages of this kind of thing:

I’m gonna File > New > Script file and add it as a variable there so I don’t have to keep eyeing this ugly monstrosity on my working file, and so that it doesn’t slow down my editor.

varsecret="def1ffffdef1ffffdef1ffffdef1ffffdef1ffff..."

Right, and now I’m gonna take a wild guess and assume these are pairs of 0–256 integers.

Maybe they’re ASCII character codes?

We’re gonna find out. Lemme put my hacker shades on.

/**
* Red button does this:
*/functionyouDontTellMeWhatToDoIllFuckingPressItAlright(){//SDSCUIFP()timeToHackTheGibson()}functiontimeToHackTheGibson(){vartheSecretCode=[]varASCII=""// Splits the string into two character chunksfor(t=0;t<secret.length;t=t+2){theSecretCode.push(secret[t]+secret[t+1])}// Convert those into ASCII and tosses 'em into a string for(u=0;u<theSecretCode.length;u++){ASCII+=String.fromCharCode(parseInt(theSecretCode[u],16))}Logger.log(ASCII)}

That code should do it, and it gives us…

…rubbish. Nothing useful whatsoever.

Oh well.

Hrm. You know what else I always think about when I see hex sequences? RGB codes. What if?

Okay. Time to put the TimeCop1983 mix in the tape deck and get serious about this.

So we take this function…

functionALLTHECOLORS(){// Same as before, but in groups of sixes, with a prepended #varrainbow=[]for(t=0;t<secret.length;t=t+6){rainbow.push("#"+secret.slice(t,t+6))}Logger.log(rainbow)}

And what we get is…

Hm, I don’t know, something about it just doesn’t smell right.

Let’s comment the ASCII part out of our timeToHackTheGibson() function and take a closer look at that hex.

Since Logger.log() truncates the result, I’ll run it locally and export it to a file.

Can you see a pattern there?

(BTW whenever you see amber or phosphorus green in a dark background, that means some serious hacking is going on.)

See? There’s one full (ff) byte for every group of four. And do you know what I think that is?

RGBA. Since they’re all the same value let’s just skip them over see what we get. To do that we change our for loop from (t=0; t<secret.length; t=t+6) to (t=0; t<secret.length; t=t+8):

And about the “hacker code”

Just in case you’d like to take a look at that jumbled, minified mess in a commented, non-messed-up form:

functionnextColor(i){// This math was straight up nicked from lolcat/lolgophervarred=parseInt(Math.sin(i+0)*127+128).toString(16)vargre=parseInt(Math.sin(i+2*Math.PI/3)*127+128).toString(16)varblu=parseInt(Math.sin(i+4*Math.PI/3)*127+128).toString(16)if(red.length<2){red="0"+red}if(gre.length<2){gre="0"+gre}if(blu.length<2){blu="0"+blu}return"#"+red+gre+blu}functionanimate(){varrainbow=[]for(t=0;t<secret.length;t=t+8){rainbow.push("#"+secret.slice(t,t+6))}// Here we find all pixels colored #ccf7e6// and save their indexes for latervarindexes=[]for(i=0;i<rainbow.length;i++){if(rainbow[i]==="#ccf7e6"){indexes.push(i)}}// This is an extra loop just to keep the colors changingvarcycles=120for(varbanana=0;banana<cycles;banana++){varvariance=0.5vartomato=getValue("A1").length*variancesetValue("A1",getValue("A1")+"a")for(t=20;t<100;t++){for(v=0;v<128;v++){if(rainbow[(t*128)+v]=="#ccf7e6"){// And here's the bit I found the most difficult.// If we were to change colors making one call for// every pixel, that'd add up to 1547 calls per// color change. Way too much, and way too slow.// So what we're doing here is finding all the // continuous sections, and making calls for// blocks/regions instead of individual pixels.// This way we end up with 115 calls per color// change, which works okay.varx=0do{x++}while(rainbow[(t*128)+v+x]==rainbow[(t*128)+v+x+1])SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].getRange((t+1),(v+1),1,x+1).setBackgroundColor(nextColor(tomato))v=v+x}}}}}