With the arrival of Google Sheet Macros, automating tasks in a spreadsheet has become simpler. But what if you require a specific task that isn’t available as a macro? Worry not, for this tutorial on Google App Script will transform you into a true master of script functions for Google spreadsheets.
What is Google Spreadsheets Script (or Google Apps Script)?
Google Apps Script is a flexible scripting language based on JavaScript, designed with built-in access to various Google Workspace applications like Google Docs, Google Sheets, Gmail, and Google Forms.
The beauty of Google Apps Script is that no installation is needed. The Script Editor, hosted on Google’s servers, remains readily available on any browser as long as you possess a Google account.
Some of the captivating features of Google Apps Script are as follows:
1. Crafting custom functions and macros
2. Creating custom menus, dialogs, and sidebars
3. Building add-ons for Google Services
4. Publishing web apps in Google Sites
5. Crafting scripts to interact with other Google products
6. Automating Google Workspace applications, including AdSense, Analytics, Drive, Gmail, and Calendar.
Google Apps Script vs. Javascript
You might be familiar with JavaScript as a complex programming language. But what sets it apart from the native Google Script Writer?
Google Apps Script is essentially a cloud scripting language based on JavaScript. Though they function similarly, Google Apps Scripts are much easier to learn, as they avoid some of the newer syntaxes found in JavaScript.
The primary distinction lies in Google Apps Script’s focus on interacting with Google products and third-party services, while JavaScript can work with any cloud language like HTML and CSS.
Embarking on the Google App Script Tutorial
Let’s delve into some basic coding with Google Apps Script to explore its simple elements and features.
Our quest involves writing a code in Google Apps Script Editor to display a friendly “Hello World” message. To embark on this journey, follow the instructions below:
1. Begin by opening a new spreadsheet in Google Sheets.
2. Click on Tools > Script Editor.
Behold, the Script Editor shall reveal itself in a separate tab, serving as the gateway to your spreadsheet’s scripting wonders.
Now, you have the liberty to rename your project, currently known as “Untitled project.” Simply click on it, type the name of your desire, and behold the new title.
The default function’s name upon opening the Script Editor is “myFunction.” Feel free to replace this default title with any name you desire.
Remember, coding in Google Apps Scripts follows the illustrious camel case format. Embrace the art of omitting spaces or punctuation when writing multiple words. Instead, separate the words by capitalizing the next one in the phrase, like a true master of camelCase.
3. Set forth and scribe the following code in Line 2 of the Script Editor:
var message = “Hello World”;
Behold, a variable (var) comes into existence, serving as a repository for a valuable piece of information. In our script, the variable “message” carries the sacred words “Hello World.” These variables are indispensable when coding, for they hold the key to your Google Apps Script endeavors in each Google app.
4. As you have written the code, press Enter after Line 2 and inscribe the next line of wisdom:
SpreadsheetApp.getUi().alert(message);
Let us decipher the wisdom of this code. Observe the following elements:
var message: This line bestows the power of storing a value within a variable. “var” signifies the creation of a variable, and you have the privilege to name it as you wish. In our tale, the variable is christened “message.”
=: Behold, the equal sign, signifying the commencement of the value attributed to our noble variable.
SpreadsheetApp: Behold, an object appears! Every object boasts its properties and methods. To access them, one must beckon the power of the dot.
getUi(): A method reveals itself, serving as a procedure or function of the object.
alert(): Another method joins the fray! It allows the browser to showcase a pop-up message enclosed within its parentheses. In our tale, the sacred variable “message” houses the treasured words “Hello World.”
Be mindful, brave coder, to inscribe a semicolon after each line. Semicolons serve as loyal sentinels, separating the statements within your sacred code.
As you embark on your coding journey, the editor interface shall graciously reveal descriptions of the methods you have written, guiding you through the mystical world of Google Script Writer.
Huzzah! The mystical descriptions of the methods are unveiled in the editor interface. Even if you are a fledgling in the realm of programming, fear not, for the Google Script Writer shall escort you through these hallowed definitions.
5. Having etched the sacred code, click the illustrious floppy disk icon to safeguard your project.
6. The moment of reckoning arrives. Click Run to breathe life into your code.
Upon this juncture, Google may request your noble authorization. Fear not, for you shall click Review Permissions, granting your project access to your precious data.
Once the code has been set into motion, the Execution log shall manifest before your eyes, documenting the exact time of your code’s awakening and the actions undertaken.
7. Return to your spreadsheet, and lo! You shall witness a wondrous box bearing the sacred words “Hello World,” accompanied by an “OK” button.
8. Bequeath a gentle press upon the “OK” button to bid farewell to the message.
Rejoice! As you glance back at the Script Editor, you shall witness the Execution log rejoicing in the completion of your noble code.
Behold, the journey’s end! Are you ready to embrace even more intricate codes?
The Saga Continues: Automation Using Google Apps Script
What if, my gallant reader, you find yourself besieged by numerous spreadsheet reports, yearning to be formatted? Fear not, for Google Apps Script holds the key to automating formatting tasks, liberating you from the chains of repetitive toil.
Let us embark on a new quest, where you shall employ the sales data before you as an example.
Your quest, should you choose to accept it, is to craft a custom format through Google Apps Script, wherein you shall:
1. Gain access to the active spreadsheet
2. Store values in noble variables
3. Identify the header and table within the sacred spreadsheet
4. Bestow new formats upon the header, table, and texts.
Gather your courage and let us venture forth into the unknown, guided by the light of knowledge.
To access the Active Spreadsheet:
In the realm of Google Sheets, behold the splendor of creating multiple sheets within a single spreadsheet. By default, the spreadsheet opens with one sheet named “Sheet1.”
If you wish to add another sheet to your noble spreadsheet, fear not. Simply click the noble “+” button, and a new sheet shall emerge, bearing the name “Sheet2.” And lo! Should you yearn for yet another sheet, “Sheet3” shall appear, and so on.
You possess the power to rename your spreadsheet. By right-clicking the current sheet, you shall find the path to “Rename.”
In your gallant quest, should you be faced with a spreadsheet of multiple sheets, you must craft a code that proclaims your dedication to the current or “active” spreadsheet.
To achieve this noble feat:
1. Awaken the Script Editor of your illustrious spreadsheet by clicking Tools > Script Editor.
Lo and behold, the Script Editor shall reveal itself, offering a blank canvas upon which you shall weave your destiny. Here, I have taken the liberty to rename the project as “Google Apps Script Tutorial” and the function as “customFormat.” You may choose to follow in my footsteps, remembering always the virtue of camel case for the function name.
2. Scribe the following code into the Script Editor:
var sheet = SpreadsheetApp.getActiveSpreadsheet();
In this act of coding brilliance, we have crafted a variable called “sheet,” wherein we have stored the power of the active spreadsheet. Thus, when the code is set in motion, the functions shall be bestowed upon our valiant Sheet1.
Behold, the power of the Google App Script Tutorial.
GetRange Google Script and Setting Value
Lo and behold, the enigmatic getRange Google Script appears, a master of identifying particular cells within the sacred spreadsheet. Fear not, for with this knowledge, you may choose to select one cell or many, enabling you to bestow new values upon the hallowed spreadsheet.
To achieve this wondrous feat:
1. As the previous lines of code have been written, press Enter, and the journey continues with the following lines of wisdom:
sheet.getRange
Within this ethereal realm, we shall grasp the range by the power of:
– getRange(a1Notation): This sacred incantation returns the range specified in A1 notation or R1C1 notation.
– getRangeByName: This oracle unveils a named range or bestows upon thee the knowledge of null if no range bears the given name. Should multiple spreadsheet sheets share the same range name, specify the sheet name without further quotation marks.
– getRangeList: This wondrous revelation reveals the RangeList collection, portraying the ranges within the same sheet as dictated by a list of A1 notations or R1C1 notations.
Lo, there exist other methods of getRange, as shall be shown in the mystical box that emerges as you scribe the code. Yet, let us focus on the three methods revealed before us.
Let us first witness the splendor of getRange(a1Notation):
For example, we seek to modify the value in cell C2, wherein “Smith” currently resides.
Let us transform it into “Morgan.” Be brave and inscribe the following code:
sheet.getRange(“C2”).setValue(“Morgan”);
Fear not, for the brave code has been executed. Save thy project and run the code to witness the metamorphosis.
Lo! Return to your spreadsheet and marvel at the sight of “Smith” transformed into the majestic “Morgan.”
You possess the power to bestow other values upon cells, be they numbers or symbols. Simply remember to encase them within the embrace of quotation marks.
And behold, for getRangeList, we shall identify the cells to be changed by highlighting them in the spreadsheet. Observe the cells marked in the upper left corner of the spreadsheet (C2:C11).
With this knowledge, scribe the following code:
sheet.getRangeList( [‘C2:C11’] ).setValue( “Morgan” );
Save your progress and run the code. Return to your spreadsheet, and lo! All the data under “Rep” shall have become “Morgan.”
You may also venture to modify other cells by adding their cell notations to the code. For example:
sheet.getRangeList([‘C2:C11’, ‘D2:D11’]).setValue(“Morgan”);
With this code, all the data in cells C2:C11 and D2:D11 shall undergo a wondrous transformation, becoming “Morgan.”
Let us celebrate the versatility of GetRange, for it grants you the power to achieve automation, even in the realm of email sending.
Modifying the Header and Table Through Google Apps Script
As our quest progresses, we shall now apply the wisdom of GetRange to a noble sheet.
Imagine, if you will, that you desire to design your spreadsheet with the following aspirations:
For the header:
– Add highlights to the texts
– Change the font color
– Alter the font style
– Align the text to the center
For the table contents:
– Adjust the font size
– Center the texts
– Add borders
Prepare your quill, and let us scribe the code:
1. To begin, store the header and table as noble variables:
var header = sheet.getRangeList([‘A1:G1’]);
var table = sheet.getDataRange();
2. Now, inscribe the adjustments you seek to make:
header.setFontStyle(“italic”);
header.setFontColor(“white”);
header.setBackground(“blue”);
header.setHorizontalAlignment(“center”);
table.setFontSize(16);
table.setHorizontalAlignment(“center”);
table.setBorder(true, true, true, true, false, true);
Lo! The code shall yield the following:
– header.setFontStyle(“italic”): This act bestows the font style of italic upon the header.
– header.setFontColor(“white”): The font color of the header shall transform into a resplendent white. The choice is yours, for you may inscribe the name of the color or its hex code.
– header.setBackground(“blue”): A radiant blue hue shall highlight the headers, proclaiming their grandeur.
– header.setHorizontalAlignment(“center”): Behold, the headers shall align themselves to the center, a sight to behold.
– table.setFontSize(16): This majestic decree shall set the font size of the texts within the table to 16.
– table.setHorizontalAlignment(“center”): The texts in the table shall embrace the center, guided by this noble command.
– table.setBorder(true, true, true, true, false, true): The sacred border shall grace the table, carefully selected by the syntax:
setBorder(top: boolean, left: boolean, bottom: boolean, right: boolean, vertical: boolean, horizontal: boolean)
Choose “true” if thou desirest a border for a specific part of the table; otherwise, inscribe “false.”
Fear not, for the Google Apps Script shall guide thee with descriptions within the editor. Embrace these revelations as they shall illuminate the path of thy code.
Save your code, and let it unfurl its magic upon the spreadsheet. Return to your domain, and rejoice! The actions inscribed in the Script Editor shall bear fruit, gifting thy active spreadsheet with the bestowed changes.
The Script Project Transcends to Other Spreadsheets
What if, dear reader, you desire to bestow the custom formatting upon another spreadsheet?
Be of good cheer, for the code you have written can journey forth to new lands, with a few adjustments along the way.
In this tale, behold another spreadsheet bearing a different set of data.
Lo! You seek to apply the same custom formatting bestowed upon the previous spreadsheet. Fear not, for the quest is within reach:
1. Return to the Script Editor of the previous spreadsheet.
2. With valiant resolve, copy the entirety of the script project, from its inception to the closing bracket, by pressing Ctrl + C.
3. Venture to the new spreadsheet, and with determination, proceed to the Script Editor by clicking Tools > Script Editor.
As the magical gates open, behold, a blank canvas awaits your command. It is here you may choose to rename the project as you desire.
4. Embrace the bravery within, and delete the contents of the Script Editor. Then, with a flourish, paste the previous code (Ctrl
+ V) containing the cherished custom formatting.
5. Save your progress, and with bated breath, run the code.
6. Rejoice! Return to your new spreadsheet, and witness the fruits of your labor. The custom function shall have been bestowed upon this new realm, mirroring the changes witnessed in the previous one.
Embrace the versatility of the Script Project, and dare to apply its wisdom to other spreadsheets, easing the path of formatting as you traverse the realms of Google Sheets.
A Glimpse of the Vastness: Moving Forward with Google Apps Script
You may harbor a thought, noble reader, that to master Google Apps Script, one must memorize an endless array of codes. But fear not, for in this realm, Google has bestowed upon thee the Class Sheet, a tome containing the secrets of Google Apps Scripts.
Truly, there exist almost no limitations to what you may achieve with Google Apps Script. You may even venture to create and modify other Google Workspace Products, such as Google Docs, Google Slides, and Google Forms, guided by the sacred teachings of Google Apps Script.
Your journey has just begun, and with each step, the mysteries of the script shall unfold before you, granting you the power to wield its magic.
In Conclusion: A Celebration of Google Spreadsheets Scripts
Rejoice, for you have unlocked the wondrous features of Google Spreadsheets Scripts! Behold the many powers it grants, allowing you to maximize your spreadsheet’s productivity without the shackles of complexity. Automation, custom functions, formatting, and more lie at your fingertips, waiting to be harnessed in your noble quest.
May the knowledge you have gained serve you well in your future endeavors, for you are now a master of the Google Apps Script, prepared to weave enchanting scripts and shape the destinies of spreadsheets.