July 28, 2021

Writing Macros in Google Sheets

How to write and run macros in Google Sheets. Watch the video below:

Writing and running macros in Google Sheets
  • Log into your Gmail account
  • On the top right corner click on the ellipsis called ‘Google apps’ and from the drop-down choices select ‘Sheets’
  • A new web page called ‘Sheets’ opens. Select ‘Blank’.
  • A new blank sheet called ‘Untitled spreadsheet’ opens.
  • Click on ‘Tools’ and select ‘<>Script editor’ from the drop-down choices
  • A new window called Apps Script opens with the name ‘Untitled project’ and the prefilled code: function myFunction() {  }
  • We can delete this code and now write our own code, for example,

function AddOne() {
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var value = cell.getValue() * 1;

This code will add 1 to a cell value.

SpreadsheetApp.getActiveSheet(): This part of the code gets the active sheet in a Google spreadsheet. The active sheet in a spreadsheet is the sheet that is being displayed in the spreadsheet User-Interface (UI).

SpreadsheetApp.getActiveSheet().getActiveCell(): This line of code returns the active cell in the active sheet.

cell.getValue(): Returns the value of the cell.

cell.setValue: Sets the value of the range

You will observe that Google sheets use the Javascript platform. We can learn more about the Google spreadsheet services at this website.

Assigning Script to Image in Google Sheets

Although we can run the script from the Apps Script window, it can be convenient to insert a button shape or picture and assign the script to these objects on the worksheet. This makes executing the script or macro quick and easy.

The best thing about Google sheets with scripts is that you share them quickly and easily with individuals or groups. You can also copy a link of the saved Google sheets and share the link with your friends and colleagues for further collaboration.

Sharing Google Sheets is Quick and Easy