Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users

Andrew Kamphey -

Hey, hackers!

I have something crazy for you... would you spend the next five minutes, with me, and learn a new programming language?

Why so fast? Because you already know how to code. This tutorial will walk you through how to code inside Google Sheets. Where to code and what to use to code.

Let me take a moment to ask. Do you code?

I want to make sure you’re on the right level.

If you don't know how to code at all, I suggest starting with my "Learn to Code in Google Sheets" course. 6 videos that walk you from knowing nothing to literally coding inside of Google Sheets. The course is available to all lifetime

Programmers, coder, hackers, if you know the following terms you are going to get a lot out of this quick tutorial.  What I’m showing you in this tutorial is not how to program, not how to code. I’m not teaching you to be a hacker. I’m showing you how to hack in Google Sheets. Use your programming skills inside of a Google Sheet.

  1. Functions
  2. Variables
  3. Javascript
  4. For Loops
  5. Logging
  6. Debugging
  7. JSON
  8. API

Why Learn Google Script in Google Sheets?

Learning specifically how to code inside Google Sheets will help you build your own tools. Will help you create dashboards for your startup with data from things like Profitwell, Stripe, etc. You can move data from your CRM into sheets and program ways to manipulate that data. Maybe you want to try an upsell. You can keep your data in a sheet and test it out quicker than having to create a new feature in your user database. You can do much more experimentation within sheets because you can see the database, and edit it directly.

As well as use Apps Script to automate business processes. Your non-technical team may know Google Sheets and not javascript. So you can write little bits of code for them to use at will. And they are comfortable with spreadsheets and specifically Google Sheets.

Let’s learn to code in Google Sheets, for programmers.

You know programming and I know Google Sheets. Yeah, let’s get crazy!

Note: You can access all the code in a google document here at BetterSheets.co/hackers. You can directly get this document and get all the code.

Also you can watch a YouTube video that shows every step of this blog posts.

But you want to read. So keep reading!

Follow the steps below and you’ll be coding in Google Sheets in no time.

  1. Go to sheet.new. Once the sheet starts to load up, go to the extension called App Script. Every Google Sheet has access to this. Now, every function looks like this:

function functionName( ) {  }

You can add in parenthesis any sort of variable names that you want to add and then use those variables and execute code inside this curly bracket.

Variables use var variableName =  ; You can use the semicolon the same way you’d use it in Javascript if you want to keep that style. It’s not necessary to use it, though.

  1. Let’s create a function right now and call it costperMille or CPM, which is a very common math problem in marketing. We want to know our CPM and we’re going to write this function right in Google Script.
function cpm( cost , count ) {
var mille = count / 1000 ;
var cpm = cost / mille ;
return cpm
}

Our function has two variables: cost and count. Inside the function, we have our variable mille. We get that number out by typing “return cpm.” (Seen at the bottom part of the code above.)

Don’t forget to save it. Tip: When you see an orange dot on the left sidebar, the one where it says Code.gs, it means you haven’t saved your work.


  1. Now we can use this function inside of Google Sheets. You can either type in the numbers or use the cell numbers as reference. In this case, they would be C2 for 450 and C3 for 3000.


Let’s keep going. Let’s tap into the infinite power of Google Sheets! It's like a database we can access directly, back-end, front-end – all one and the same. No need to learn HTML, just Javascript. We’re coding so we can access a value inside of a Google Sheet.

  1. In cell A1, let’s put in the amount of sales we have. Remember, we’re on sheet one. Got that? Okay, now we’ll go to Google Script and enter more codes.



We can change the function name. Then you’ll need the parenthesis and the curly brackets. Inside those curly brackets, we’re going to type in var cellData. Again, we can name that variable anything we want. We’re using the Spreadsheet App and it's going to be indicated in pink.

  1. Next, enter a dot after SpreadSheetApp and then type getActive. There will be a list of suggestions when you type that. We want getActiveSpreadsheet and here’s one trick: Add parenthesis after this.
  2. Then we follow that with .getSheetByName and again, add parenthesis. In between the parenthesis, in quote, type in Sheet1 so we can get to the next piece of code.
  3. Now type in .getRange and then we’re going from the entire spreadsheet to the active spreadsheet to the sheet name. We’re going down in hierarchy for this. Then we’ll get the range of that sheet or the cell.
  4. Once we have the range A1, we type in getValue(); and we can end that in semicolon if we want.
  5. Now we have all of this – the hierarchy down to the exact cell. Now we want A1 and we will return it. Type return cellData and then make sure to save it.

Below is the code you’ll enter.

function getDataFromA1onSheet1() {
var cellData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”).getRange(“A1”).getValue();
return cellData
}
  1. Back in Google Sheets, we can have A1 anywhere. Copy the entire code getDataFromA1onSheet1 and then add the parenthesis. For this tutorial, we’ll paste that code in E6. Just run that code and you’ll get the number 50, where it got the data from A1. Isn’t that cool?

Any data on this spreadsheet we can access by its range, by its sheet name, by its hierarchy. A sheet, an active spreadsheet, the name of the sheet, and the range that it's in. If you wish to copy this specific script again, go to BetterSheets.co/hackers and you can get this Google Doc exactly for yourself. Copy it, use it. Copy this code, try it for yourself.

Using the same data, but on a sales page this time.

Wasn’t that cool? Now let’s try something else. Using the data we have, let’s apply it to a sales page, where we want to copy that to a log. Copy all of this code and add it to the existing code that we have.

function copySalesDataToSalesLog() {
// Get data from sheet Sales Summary "what you want to copy"
// Get sheet & range of Sales Log "where you want to copy it to"
// Insert a row above the top row
// get range to write timestamp
// write timestamp new Date()
// copy data
}

Now what does this code do? Nothing. Why will this do nothing? It's because we've commented it out. So in Google Script, we can do two slashes in front of any text we want. That is commenting out. It won't actually run, just like in any other programming language. With the code above, we've written out in words what we want to do.


Create sheets: “Sales Summary” and “Sales Log”

We want to get data from the sheet sales summary. So we need a sheet called a Sales Summary and we want to copy that to a sheet called Sheet Log.

What we’re doing is to get a sales amount. We want to actively edit this every day, but we want to copy it to this log. We’re doing this so that it's time stamped and it has a reverse chronological log of all of the sales of every day.

Now here’s what we’ll do:

  1. Get data from the sheet sales summary, what we want to copy.
  2. Get the sheet and range of the sales log where we want to copy it to.
  3. Insert a row above the top row, just in case there's no more rows.
  4. Get the range and write a timestamp.
  5. The timestamp we'll use is new Date()
  6. Copy the data.
function copySalesDataToSalesLog() {
  // get data from sheet Sales Summary "what you want to copy"
  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales Summary").getRange("A1")
 
  // get sheet Sales Log "where you want to copy it to"
 
  var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales Log")
  var destinationRange = destinationSheet.getRange(1,1,1,1)
 
   // insert row above the top row
   destinationSheet.insertRowBefore(1)
 
   // get range to write timestamp
   var timestampRange = destinationSheet.getRange(1,2,1,1)
 
   // write timestamp
   var timestamp = new Date()
 
   timestampRange.setValue(timestamp)
  //copy data
   data.copyTo(destinationRange,{contentsOnly:true})
 
  //By default, values ​​and formatting are copied, but you can override them using advanced arguments.
}


Let’s walk through the code I’ve written:

  1. We have function copySalesDataToSalesLog(), our variable data just as we did before. We get the range of the name of the page and A1.
  2. Get the range that we want to go to and then insert a row, literally just the destinationSheet.insertRowBefore()
  3. Insert number one, so it now looks like this: destinationSheet.insertRowBefore(1)
  4. Then we have the range of the timestamp. The timestamp is literally the new date, like this: new Date()
  5. Set that date as a value, timestamp, so it should look like this:  timestampRange.setValue(timestamp)
  6. Then we’re going to copy it to the destination sheet. By default, the values in formatting are copied, but we can override them here with contentsOnly:true

We're only copying the contents. That is key because sometimes we don't want to copy that formatting, so we're gonna hit command s and.

  1. Now hit command S and save.

Now we need to authorize in order to run this code:

  1. Click on the dropdown on the row on top of the page, where it says “cpm”, and you’ll see other options to choose from. Choose copySalesDataToSalesLog and then hit Run.


  1. You’ll get the pop-up for Authorization Required.


We only have to do this once when we run the code for the first time when we have new scopes. Sometimes, you always have to test it.

  1. Run your sales data and there you go! Now we have a timestamp and a sales amount.


What if something doesn’t work and we don’t actually see our answer?

Add this piece of code at the end: Logger.log(data.getValue()

This is great for debugging. Now hit Run and we have the 50 right there.


So this allows us to log little points to debug along the way. Really, really cool inside this IDE, right?

Run cron jobs

We’re hackers because we’re lazy. We don’t want to click one button everyday. So let’s set this up!

Refer to the following code for this: function copySalesDataToSalesLog()

  1. On the left side of the page, we have this timer button that says “triggers.” We're going to  click “trigger.”


  1. On the bottom right, click on the blue button that says “Add Trigger.”
  1. A modal will appear and you should make the following changes:
  2. In the first field where it says “Choose which function to run”, select CopySalesDataToSalesLog.
  3. The second field where it says “Choose which deployment should run” can’t be changed, so keep that as it is.
  4. In the third field where it says “Select event source”, we’re going to change that to Time-driven.
  5. Next is the field that says “Select type of time based trigger.” Select Day Timer. Once we do that, we have a time based trigger, so we can do this everyday.
  6. The last field that says “Select time of day” is where we can select the amount, the day time period we want to run it. In Google Sheets, you can’t set a specific exact time, but you can set an hour of the day to run. For this walk through, let’s set it to “9am to 10am.”
  7. Now we have a trigger that will run every single day between 9 AM and 10 AM.

This trigger we have created is going to do exactly what we did before:



Note: Again, if I'm going too fast in this video, you can go to BetterSheets.co/hackers. All of this is in Google Docs.

But, wait! There’s more.

Thibault asked me – Yes! The same Thibault who made Tweet Hunter asked me, “How can we access an API through Google Script?” For this example, let’s use omdbapi.com and get your own API key. Then follow the steps below:

  1. Create a new sheet called API Key. It can sit right beside the Sales Log sheet.
  2. Place your API Key on A1.
  3. Get the JSON. The main piece of information, the biggest piece of information you need to know is UrlFetchApp.fetch
  4. Go back to our code editor. If we don't want to continue writing new scripts to what we were working on, we can always add a new script. Click on the plus icon on top and choose “Script” from the dropdown options. Then call it “API.”


  1. Paste the JSON code. It means get our API key, but it’s exactly the same code we had before, which is SpreadsheetApp.getActiveSpreadsheet().getSheetByName("apikey").getRange("A1").getValue();

It’s exactly same. We're accessing that information from the spreadsheet.

  1. Now we have the URL of omdbapi.com. You can add in the url, the API key and the title you want to fetch.
  2. To get the value for getJSON(title) and what that does is that it grabs the API key from A1. Create a new tab called Movies. Type =JSON(A1) on B2. Make sure you enter a movie title in A1 so you won’t get an error when you run the code.
  3. Now you get to see all the information about your chosen movie (i.e. “Hackers”) in B2.

It gets even better. One word: Poster.

Yes, we can actually get the poster! So here’s the function:

function getPoster(title){
var data = JSON.stringify(getJSON(title))
var language = data.split("Poster")
var plot = language[1].split("Ratings")
var poster = plot[0].split('https')
var poster2 = poster[1].split('jpg')
return "https" + poster2[0] + "jpg"
}

Again, you can read this inside of BetterSheets.co/hackers

  1. In the spreadsheet (still in the Movies tab), we’ll change things. Instead of the current data, we’re going to use =getPoster(A1).
  2. In that same code, we want to put this around the image function. This is a Google Sheets formula image. It takes in a URL and then shows an image. Your code should now look like this: =image(getPoster(A1))

We can change the title (A1) to anything we want and we can get the movie poster. We can get any poster with this API in Google.

And now we know Google Scripts. Isn’t that cool? If you want to follow along the code and get the code again, go to BetterSheets.co/hackers.

Go get it, go use it, learn how to program in Google Sheets.

And watch the whole youtube video: