Denice R. Preston
Google Apps Script may appear familiar to people who understand Microsoft’s Visual Basic for Applications (VBA) language. But Google’s code offers more integrations and the capability to integrate workflows across documents, spreadsheets, and even presentations seamlessly.
Authors are vetted experts in their fields and write on topics in which they have demonstrated experience. All our content is peer-reviewed and validated by Toptal experts in the same area.
Influential leaders understand that time is the most critical aspect… Nothing else could distinguish successful executives more than their loving and caring for time.
Time is our most precious resource. We should use it for the highest impact and value-added activities that could be done, not just because they usually have the most value in terms of money but also to push ourselves to the limit and improve the satisfaction we get from our jobs.
Recording and Using Macros in Google Sheets
If you’ve spent a lot of time using Excel or Excel spreadsheets, you will come across Excel’s VBA (Visual Basic for Applications) macro interface at some moment. You can do this by recording or writing them by yourself or piggybacking on those created by others.
Macros can be a fantastic method of automating routine and repetitive processes. VBA may not be a programming language you have put in much effort to master. However, its appeal was that it didn’t need to be productive. You could create the macros you want to use. It was easy to create the workflow you wish to automate, enter the code, and make the minor adjustments necessary to create a more universal macro.
In a few ways, VBA is a great and often overlooked lesson on how to get people who are not tech-savvy to programming. The method of recording actions and being able to populate the code for review later on is a more practical method of learning than simply the course of reading books and watching tutorials.
The same recording capabilities of VBA can be found within Google Sheets. Here’s a quick example of how you can use it:
Let’s get started with some examples of data employing an ImportHTML query for importing tables. In this case, I’ve downloaded a list from Wikipedia of the 15 most significant hedge funds around the globe. This is not a surprise that this is a hypothetical instance; the goal is to make you concentrate on the specific application and less on the subject.
When you’re done, press the Save button in the macro window on the bottom and give the macro a name and an alternative keyboard shortcut.
The process will conclude here for simple actions that could be reproduced precisely using these steps, and you could begin utilizing the macro right from the beginning. In this scenario, however, a few changes must be made before the code can be used. For instance, the document copied must be given a new name every time. Let’s explore how we can do this.
Writing Google Apps Script Manually
We will now see the foundations of the Google Apps script for the first time. It is the platform for programming which runs through Google’s server. It powers our macros and lets you create highly complex workflows and add-ons to the apps. It is a great tool to automatize spreadsheet tasks and everything connected to the Google G Suite.
The language used in programming that is part of Apps Script can be described as JavaScript, one of the more well-known programming languages. This means plenty of information is available to those who want to learn more about it. However, as with VBA, there’s no need to use the Record feature and perform the steps you’d like to repeat. The result of the recording may look a bit rough and likely will not align with the work you’d like to accomplish; however, it’ll provide an adequate base. Now, let’s do it again for the script that we recorded.
When recording, it is logical to ensure that you don’t record any additional steps that occur by mistake that you do not want to be recorded during the recording process. However, sometimes, it’s difficult to avoid. For instance, something as easy as choosing another cell before pressing the stop Recording button can be recorded and repeated each subsequent time you use the script. The first step to editing our writing is to tidy it up and eliminate any of these steps.
Advanced Google Apps Script Examples: Connecting Sheets to Google Drive and Slides
All are starting to develop; however, what happens if the output we’re looking for isn’t an Excel spreadsheet but rather a PowerPoint? If this is the case and it is, then the majority of work here could be done manually, and we’ve probably not gained much time when we have to perform this regularly.
Let’s look at the possibilities of creating a compelling presentation using the information from our spreadsheet.
This exercise is now more difficult due to two reasons:
We’ll need to be familiar with how to work with Google Slides (and Google Drive) and Sheets.
In Slides or while working with G Suite Apps, there isn’t a “Record macro” functionality available. This means that you’ll need to be knowledgeable about Apps Script (and confident using the manuals for every G Suite product).) to write your scripts.
The next step is to obtain the ID for this template since you will be referencing the template in any script. You will have encountered this ID numerous times as it is, in reality, the random sequence of numbers and characters you can find within the address bar of your web browser.
The next step is to include the following lines in our script. The prompt will be again for authorization, but this time for access to Google Drive.
The situation is more exciting since we’ve changed the initial page to show the current date. In Slides like Sheets, you use things (represented in classes) with characteristics and procedures (i.e., connected capabilities). They are arranged into a hierarchy that includes spreadsheetsApp, DriveApp, or SlidesApp as the top-level object. In the above code snippet, it is necessary to go through the hierarchy step by step to arrive at the element that we wish to edit, which is, in this case, the text within the text box. In practical terms, we have to go through the Presentation, Slide, PageElement, and Shape objects until we get to the TextRange object we want to edit.
Determining the type of object you’re working with is a challenge, and the problems that arise when you try the application of an operation on the wrong thing may be difficult to resolve. Unfortunately, help function and error messages available in the Script Editor themselves do not always offer a lot of direction. The good news is that this attention can, at a minimum, increase the quality of your control methods.
