The Secrets of Google Sheets: Mastering Macros, Functions, and Triggers

Google Sheets is a widely used online application that allows users to create and format spreadsheets with the ability to edit and collaborate with others in real-time. One way to extend the power of Google Sheets is by using Google Apps Script.

Google Apps Script enables you to customize and automate workflows within your spreadsheet. Some of the most useful features for customizing a Google spreadsheet are macros and functions.

What is a function?

In programming, a function is a “chunk” of code that can be used multiple times instead of writing it repeatedly in different places. It contains instructions used to create an output from its inputs.

What is a macro?

In Google Sheets, a macro is a recorded series of actions that can be used to automate repetitive tasks. Macros can significantly reduce the time spent on manual and tedious processes by automating them. Once the set of actions is recorded (or manually defined in the code/function), they can be scheduled to run according to time specifications or be triggered by a specific event (simple or custom).

Using functions and macros

Start by creating a function

1. Sign in to Google Sheets and open the spreadsheet document in your browser.
2. In the top navigation bar, go to Extensions > Apps Script.
3. The Apps Script site should open in a new window or tab. Make sure you are on the “Editor” tab in the left navigation bar of the screen.
   
4. Define the body of the function (written in JavaScript). Rename this function (myFunction()) using a reserved function name to use a “Simple Trigger”. Otherwise, rename it more descriptively without using a reserved function name to use it in a custom trigger.
5. Press “Ctrl + C” on your keyboard to save the function.

Import a function (with a simple trigger) as a macro

1. Create a function by following the steps in the above section, “Steps to create a function.”
2. Make sure to use a reserved function name for your macro.
3. Go back to the Google Sheets document.
4. Go to Extensions > Macros > Import Macros.
5. Find the desired function and click “Add Function”. Close the dialog box.
6. Go to Extensions > Macros > Import Macros and check that your function appears in the list.
   

Create a custom trigger for a function

1. Create a function by following the above steps.
2. Make sure not to use a reserved function name for your macro.
3. Stay on the Apps Script site and go to the “Triggers” tab in the left navigation bar.
4. In the bottom right, click “Add Trigger”.
   
5. Select “On change” from the “Event type” dropdown menu. Click “Save”.

Conclusion

Implementing custom code into your Google spreadsheet can be very useful, and it can be as simple as creating a macro and importing it with a simple trigger or creating a macro and associating it with your own custom trigger in Google Apps Script.

If you have any questions, feel free to contact us. We hope you found this article helpful and look forward to your feedback.

Crawlan.com