How to Write a Script in Google Spreadsheet?

Google Apps Script is an environment, from which you can interact with Google G Suite Services like Google Docs, Sheets, Forms and so on. It can be really helpful if you want to automate some of your routine job. With some basic knowledge of JavaScript you can create really great things, that would save you a lot of your time. Even if you still don’t know JavaScript, Apps Script could be a great opportunity to try yourself in programming. And that`s why: it is really a pretty logical and user-friendly environment. Believe me, that’s not so scary as it looks like.

Let`s learn some basics on the example of Google Spreadsheets script.

How to write a script in Google Spreadsheet?

Create an empty spreadsheet document and go to Tools⇒Script Editor. So that`s how Google Apps Script console looks like:

Probably the first code that you`ll see in it – will be a function called “myFunction”. If you don’t know JavaScript, function – is like some kind of box, that`s keeps some code inside. So if you want to run a code in it, you should run that function. Let`s create our first code: you`ll write your name to the specific cell of your spreadsheet document.

At first – imagine doing that task without any kind of code. From what would you start after opening your browser? Right answer is – from opening Google Spreadsheet.

So the beginning of your code would be the command:

SpreadsheetApp

Write it into the function “myFunction”. With that code you say something like: “Hey, I want something from google spreadsheets application!”. Now put a dot and you will see the methods – things you can do next with that application. Next we would like to get our active spreadsheet. So now we will have:

function myFunction() {
SpreadsheetApp.getActiveSpreadsheet()
}

As you probably know – spreadsheet can have a lot of sheets in it. We would like to get an active one. So add .getActiveSheet() to your code. So now we are in Google Spreadsheets App, have chosen active spreadsheet and active sheet in it. It`s time to choose the range of the sheet, where we want to write down our name. Add .getRange and choose the first method from the list. You should have this:

function myFunction() {
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, column)
}

Now we see that the method “getRange” has two parameters: a row and a column. So if we want to get the C2 cell – we should get the second row and the third column in our sheet (pretty logical). Change the parameters inside the getRange method to relevant numbers. Now we`ve got our cell, so we want to put a value in it. You can do it with the “.setValue” method. It also has the parameter, which will be our text. In JavaScript you should write text (string values) in quotes, that way program will understand that you mean string, not code command. Write down your name as a parameter, and in the end you should get something like this (with your values of course):

function myFunction() {
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,3).setValue("Serhii Puzyrov");
}

In JavaScript you should put semicolons in the end of your command. Now run the script with a button (it looks like a Play button). When you run it for the first time – you should give some permissions to your script. After running this sript you should see your name in the relevant cell of your active spreadsheet document. Now you see, that it is not so hard even without any knowledge of JavaScript.

All you need now – is to learn some other methods, and some JS tricks like loops or if-else statements.

There are some other examples of SpreadsheetApp code:

//Get a spreadsheet by url-link:

SpreadsheetApp.openByUrl(https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXX);

//Get a sheet by name:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");

//get value from A1 cell:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1).getValue();

You can learn more methods for SpreadsheetApp HERE