No More Visual Basic… Use AppScript & JavaScript ES5 In Google Sheets!

It’s been years since I last wrote an article about using Visual Basic for Applications (VBA) to create Google Sheets add-ins. And while there were many great comments on that old article, it was clear that the time had finally come to update it. The problem with the old VBA and Google Sheets solution was that when you share your work with others, they need to have a copy of your code in order to use it. That makes sense for personal projects, but less so for “open source” type projects that you want other people to use and contribute to.

So this kind of brings me to why I’m writing this article today…

The Rise of AppScript & JavaScript ES5 In Google Sheets!

As more people learn how easy AppScript makes creating add-ins for Google Apps Script (and its new version — Apps Script 2.0), more developers are using it as a foundation for building their own apps. Because AppScript is built on top of JavaScript ES5, this means that you can leverage all the modern features of JavaScript ES5 without having to worry about whether your users have support or not! For example, if you’re working with HTML DOM elements, you can take advantage of the following:

For those who are new to Google Apps Scripts and/or have never heard about AppScript before, I would highly recommend reading my two previous articles here on GSuiteGeeks titled Introducing JavaScript ES6 In Google Docs & Sheets and Advanced JavaScript Features In Apps Script 2.0 For Beginners if you’re interested in learning more about these amazing tools! Or just continue reading below.

Putting JavaScript ES5 To Use In Google Sheets

In order to begin putting what you’ve learned about JavaScript ES5 to use in Google Sheets, you’ll need to complete a few steps:

Open your preferred web browser (I recommend Google Chrome) and go to the following URL: https://script.google.com/macros/ Create a new script and give it a name that you’ll easily recognize When the editor opens up, click on the “Resources” menu option and select “Run This Macro On All Sheets” Lastly, paste the code below into the editor window and save your changes before you close out of it. You can also download this file via my Google Drive here!

//Step 1: Declare your variables… var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’); var app_version = ‘1.0’;

//Step 2: Check if AppScript is installed… if (!ScriptApp.isProjectAvailable(‘Your Project Name’)) { //If not, then tell user that AppScript project must be made available before running } else { //If yes, then continue execution }

//Step 3: Define CSS for stylesheet… var myCSS = [ ‘#myCell { font-size:14px; font-family:”Calibri”,”sans-serif”; text-align:’ + ‘center; color:#ccc;}’, ‘#myTable { border-collapse:’ + ‘collapse; width:’ + ‘100%; margin:auto;}’, ‘.myClass { padding:’ + ‘(10px 10px 20px 10px);’ + ‘margin: auto;}’ ];

//Step 4 : Execute custom function called “myFunction”… app_version = ‘1.0’; function myFunction() { //Get the first visible spreadsheet from all open spreadsheets… var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’); //Delete rows 1 – 5, since they are reserved for header information… ss.deleteRows(1,5); //Define table styles & define table dimensions in pixels… var tableStyle = {‘width’:800}; myCSS.forEach(function(style){ style += ‘;’ + tableStyle; }); //Now set the css to be used in the current sheet using the “stylesheet” property… currentSheet.setStyle(‘stylesheet’, myCSS); }

//Step 4: Execute custom function called “myFunction”… app_version = ‘1.0’; function myFunction() { //Get the first visible spreadsheet from all open spreadsheets… var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’); //Delete rows 1 – 5, since they are reserved for header information… ss.deleteRows(1,5); //Define table styles & define table dimensions in pixels… var tableStyle = {‘width’:800}; myCSS.forEach(function(style){ style += ‘;’ + tableStyle; }); //Now set the css to be used in the current sheet using the “stylesheet” property… currentSheet.setStyle(‘stylesheet’, myCSS); }

Using The “stylesheet” Property

In the code above, you’ll notice that I used the “stylesheet” property to define my own custom stylesheet. That’s because this property allows you to add or remove styles from your sheet! And by using JavaScript ES5, this means that you can use all of the new CSS3 properties and even dynamically create new ones (such as for creating gradients) if needed! But how exactly do you use it? It’s quite simple really… just define a string object containing your desired CSS code:
myCSS = [ ‘#myCell { font-size:14px; font-family:”Calibri”,”sans-serif”; text-align:’ + ‘center; color:#ccc;}’, ‘#myTable { border-collapse:’ + ‘collapse; width:’ + ‘100%; margin:auto;}’, ‘.myClass { padding:’ + ‘(10px 10px 20px 10px);’ + ‘margin: auto;}’ ]; currentSheet.setStyle(‘stylesheet’, myCSS);

Note: In addition to defining custom CSS for your Google Sheet, don’t forget that you can also create/modify custom CSS for individual cells too! You can learn more about these two options in my other article titled How To Use Google Sheets Custom Stylesheets & Cell Stylesheets For Better Results!