Sheet Sizer! Build A Tool To Measure Your Google Sheets Size With Apps Script

In this tutorial, you’ll use Apps Script to build a tool called Sheet Sizer, to measure the size of your Google Sheets!

Google Sheets has a limit of 10 million cells, but it’s hard to know how much of this space you’ve used.

Sheet Sizer will calculate the size of your Sheet and compare it to the 10 million cell limit in Google Sheets.

Sheet Sizer

Sheet Sizer: Build a sidebar to display information

Step 1:
Open a blank sheet and rename it to “Sheet Sizer”

Step 2:
Open the IDE. Go to Tools > Script editor

Step 3:
Rename your script file “Sheet Sizer Code”

Step 4:
In the Code.gs file, delete the existing “myFunction” code and copy in this code:

/**
* Add custom menu to sheet
*/
function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Sheet Sizer')
    .addItem('Open Sheet Sizer', 'showSidebar')
    .addToUi();
}

/**
* function to show sidebar
*/
function showSidebar() {
 
  // create sidebar with HTML Service
  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Sheet Sizer');
 
// add sidebar to spreadsheet UI
  SpreadsheetApp.getUi().showSidebar(html);
}

There are two functions: onOpen, which will add the custom menu to your Sheet, and showSidebar, which will open a sidebar.

The text between the /* ... */ or lines starting with // are comments.

Step 5:
Click the + next to Files in the left menu, just above the Code.gs filename.

Add an HTML file and call it “Sidebar”. It should look like this:

Apps Script HTML files

Step 6:
In the Sidebar file, on line 7, between the two BODY tags of the existing code, copy in the following code:

<input type="button" value="Close" onclick="google.script.host.close()" />

This code adds a “Close” button to the sidebar.

Your Sidebar file should now look like this:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

    <input type="button" value="Close" onclick="google.script.host.close()" />

  </body>
</html>

Step 7:
Don’t forget to hit Save

Step 8:
Select the Code.gs file, then select the onOpen function in the menu bar (from the drop down next to the word Debug). Then hit Run.

Step 9:
When you run for the first time, you have to accept the script permissions. If you see an “App isn’t verified” screen, click on Advanced, then “Go to…” and follow the prompts. (More info here.)

Step 10:
After authorizing the app in step 8, jump back to your Google Sheet. You should see a new custom menu “Sheet Sizer” in the menu bar, to the right of the Help menu.

Click the menu to open the sidebar.

Step 11:
Close the menu using the button.

Here’s what you’ve built so far:

Google Sheets sidebar with Apps Script

Sheet Sizer: Add a new button and functionality to the sidebar

Step 12:
In the Sidebar file, after the first BODY tag, on line 6, and before the INPUT tag on line 7, add a new line.

Paste in the new button code:

<input type="button" value="Get Sheet Size" onclick="getSheetSize()" />

When clicked, this will run a function called getSheetSize.

Step 13:
Add the getSheetSize function into the Sidebar file with the following code.

Copy and paste this after the two INPUT tags but before the final BODY tag, on line 9.

<script>
function getSheetSize() {
  
  google.script.run.auditSheet();

}
</script>

When the button is clicked to run the getSheetSize function (client side, in the sidebar), it will now run a function called auditSheet in our Apps Script (server side).

Step 14:
Go to the Code.gs file

Step 15:
Copy and paste this new function underneath the rest of your code:

/**
* Get size data for a given sheet url
*/
function auditSheet() {
  // get Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
 
  // get sheet name
  const name = sheet.getName();

  // get current sheet dimensions
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();
  const totalCells = maxRows * maxCols;

  // output
  SpreadsheetApp.getUi().alert(totalCells);
}

This gets the active Sheet of your spreadsheet and calculates the total number of cells as max rows multiplied by max columns.

Finally, the last line displays an alert popup to show the total number.

Step 16:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.

When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:

Sheet Sizer sidebar

Sheet Sizer: Display the Sheet size in the sidebar

Step 17:
Delete this line of code in the auditSheet function:

SpreadsheetApp.getUi().alert(totalCells);

Step 18:
Paste in this new code, to replace the code you deleted in Step 4:

// put variables into object
const sheetSize = 'Sheet: ' + name +
  '<br>Row count: ' + maxRows +
  '<br>Column count: ' + maxCols +
  '<br>Total cells: ' + totalCells +
  '<br><br>You have used ' + ((totalCells / 5000000)*100).toFixed(2) + '% of your 10 million cell limit.';

return sheetSize;

Now, instead of showing the total number of cells in an alert popup, it sends the result back to the sidebar.

Let’s see how to display this result in your sidebar.

Step 19:
Go to the Sidebar file and copy this code after the two INPUT tags but before the first SCRIPT tag:

<div id="results"></div>

This is a DIV tag that we’ll use to display the output.

Step 20:
Staying in the Sidebar file, replace this line of code:

google.script.run.auditSheet();

with this:

google.script.run.withSuccessHandler(displayResults).auditSheet();

This uses the withSuccessHandler callback function, which we’ve called: displayResults

It runs when the Apps Script function auditSheet successfully executes on the server side. The return value of that auditSheet function is passed to a new function called displayResults, which we’ll create now.

Step 21:
Underneath the getSheetSize function, add this function:

function displayResults(results) {
  // display results in sidebar
  document.getElementById("results").innerHTML = results;
}

When this function runs, it adds the results value (our total cells count) to that DIV tag of the sidebar you added in step 7.

Step 22:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.

When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:

Apps Script to measure Sheet Size

Sheet Sizer: Handle multiple sheets

Step 23:
Modify your auditSheet code to this:

/**
* Get size data for a given sheet url
*/
function auditSheet(sheet) {

  // get spreadsheet object
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // get sheet name
  const name = sheet.getName();

  // get current sheet dimensions
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();
  const totalCells = maxRows * maxCols;

  // put variables into object
  const sheetSize = {
    name: name,
    rows: maxRows,
    cols: maxCols,
    total: totalCells
  }

  // return object to function that called it
  return sheetSize;

}

Step 24:
In your Code.gs file, copy and paste the following code underneath the existing code:

/**
* Audits all Sheets and passes full data back to sidebar
*/
function auditAllSheets() {

  // get spreadsheet object
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // declare variables
  let output = '';
  let grandTotal = 0;

  // loop over sheets and get data for each
  sheets.forEach(sheet => {

    // get sheet results for the sheet
    const results = auditSheet(sheet);
    
    // create output string from results
    output = output + '<br><hr><br>Sheet: ' + results.name +
      '<br>Row count: ' + results.rows + 
      '<br>Column count: ' + results.cols +
      '<br>Total cells: ' + results.total + '<br>';

    // add results to grand total
    grandTotal = grandTotal + results.total;

  });

  // add grand total calculation to the output string
  output = output + '<br><hr><br>' + 
    'You have used ' + ((grandTotal / 5000000)*100).toFixed(2) + '% of your 10 million cell limit.';

  // pass results back to sidebar
  return output;

}

This adds a new function, auditAllSheets, which loops over all the sheets in your Google Sheet and calls the auditSheet function for each one. The results for each Sheet are joined together into a result string, called output.

Your Code.gs file should now look like this.

Step 25:
Jump back to your Sidebar file and replace this line of code:

google.script.run.withSuccessHandler(displayResults).auditSheet();

with this:

google.script.run.withSuccessHandler(displayResults).auditAllSheets();

The callback function is the general auditAllSheets function, not the specific individual sheet function.

Step 26:
Back in your Google Sheet, add another sheet to your Google Sheet (if you haven’t already) and run Sheet Sizer.

It will now display the results for all the sheets within your Google Sheet!

Sheet Sizer: Add CSS styles

This step is purely cosmetic to make the sidebar more aesthetically pleasing.

Step 27:

Add these CSS lines inside the HEAD tags of the sidebar file:

<!-- Add CSS code to format the sidebar from google stylesheet -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

<style>
  body {
    padding: 20px;
  }
</style>

Step 28:

Add DIV BLOCKS to the elements of the sidebar:

<div class="block">
  <input type="button" class="create" value="Get Sheet Size" onclick="getSheetSize()" /> 
</div>

<div class="block">
  <input type="button" value="Close" onclick="google.script.host.close()" />
</div>

<div class="block">
  <div id="results"></div> 
</div>

Sheet Sizer: Global Variables

Step 29:

One final improvement from me is to move the 10 million cell limit number into a global variable.

Currently, it’s buried in your script so it’s difficult to find and update if and when Google updates the Sheets cell limit.

It’s not good practice to hard-code variables in your code for this reason.

The solution is to move it into a global variable at the top of your Code.gs file, with the following code:

/**
 * Global Variable
 */
const MAX_SHEET_CELLS = 10000000;

And then change the output line by replacing the 5000000 number with the new global variable MAX_SHEET_CELLS:

// add grand total calculation to the output string
output = output + '<br><hr><br>' + 
  'You have used ' + ((grandTotal / MAX_SHEET_CELLS)*100).toFixed(2) + '% of your 10 million cell limit.';

Here is your final Sheet Sizer tool in action:

Sheet Sizer

Click here to see the full code for the Sheet Sizer tool on GitHub.

Next steps: have a go at formatting the numbers shown in the sidebar, by adding thousand separators so that 1000 shows as 1,000 for example.

Control Your Nest Thermostat And Build A Temperature Logger In Google Sheets Using Apps Script

If you have a Nest thermostat at home, you can access it from your Google Sheet by using Google Apps Script to connect to the Smart Device Management API.

It means you can do some cool stuff like build a virtual, working Nest thermostat in your Google Sheet:

Continue reading Control Your Nest Thermostat And Build A Temperature Logger In Google Sheets Using Apps Script

The Complete Guide to Simple Automation using Google Sheets Macros

Google Sheets Macros are small programs you create inside of Google Sheets without needing to write any code.

They’re used to automate repeatable tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.

For example, you might apply the same formatting to your charts and tables. It’s tedious to do this manually each time. Instead you record a macro to apply the formatting at the click of a button.

In this article, you’ll learn how to use them, discover their limitations and also see how they’re a great segue into the wonderful world of Apps Script coding!

Contents

  1. What are Google Sheets macros?
  2. Why should you use macros?
  3. How to create your first macro
  4. Other options
  5. Best Practices for Google Sheets Macros
  6. Limitations of Google Sheets Macros
  7. A peek under the hood of Google Sheets Macros
  8. Example of Google Sheets Macros
  9. Resources

1. What are Google Sheets macros?

Think of a typical day at work with Google Sheets open. There are probably some tasks you perform repeatedly, such as formatting reports to look a certain way, or adding the same chart to new sales data, or creating that special formula unique to your business.

They all take time, right?

They’re repetitive. Boring too probably. You’re just going through the same motions as yesterday, or last week, or last month. And anything that’s repetitive is a great contender for automating.

This is where Google Sheets macros come in, and this is how they work:

  • Click a button to start recording a macro
  • Do your stuff
  • Click the button to stop recording the macro
  • Redo the process whenever you want at the click of a button

They really are that simple.

^ Back to Contents

2. Why should you use macros in Google Sheets?

There’s the obvious reason that macros in Google Sheets can save you heaps of time, allowing you to focus on higher value activity.

But there’s a host of other less obvious reasons like: avoiding mistakes, ensuring consistency in your work, decreased boredom at work (corollary: increased motivation!) and lastly, they’re a great doorway into the wonderful world of Apps Script coding, where you can really turbocharge your spreadsheets and Google Workspace work.

^ Back to Contents

3. Steps to record your first macro

Let’s run through the process of creating a super basic macro, in steps:

1) Open a new Google Sheet (pro-tip 1: type sheets.new into your browser to create a new Sheet instantly, or pro-tip 2: in your Drive folder hit Shift + s to create a new Sheet in that folder instantly).

Type some words in cell A1.

2) Go to the macro menu: Tools > Macros > Record macro

Google Sheets macro menu

3) You have a choice between Absolute or Relative references. For this first example, let’s choose relative references:

Macro with relative reference

Absolute references apply the formatting to the same range of cells each time (if you select A1:D10 for example, it’ll always apply the macro to these cells). It’s useful if you want to apply steps to a new batch of data each time, and it’s in the same range location each time.

Relative references apply the formatting based on where your cursor is (if you record your macro applied to cell A1, but then re-run the macro when you’ve selected cell D5, the macro steps will be applied to D5 now). It’s useful for things like formulas that you want to apply to different cells.

4) Apply some formatting to the text in cell A1 (e.g. make it bold, make it bigger, change the color, etc.). You’ll notice the macro recorder logging each step:

Macro logging step

5) When you’ve finished, click SAVE and give your Macro a name:

Save macro

(You can also add a shortcut key to allow quick access to run your macro in the future.)

Click SAVE again and Google Sheets will save your macro.

6) Your macro is now available to use and is accessed through the Tools > Macros menu:

select macro menu

7) The first time you run the macro, you’ll be prompted to grant it permission to run. This is a security measure to ensure you’re happy to run the code in the background. Since you’ve created it, it’s safe to proceed.

First, you’ll click Continue on the Authorization popup:

Macro authorization

Then select your Google account:

Macro choose Google account

Finally, review the permissions, and click Allow:

Macro grant permissions

8) The macro then runs and repeats the actions you recorded on the new cell you’ve selected!

You’ll see the following yellow status messages flash across the top of your Google Sheet:

Macro running script

Macro finished script

and then you’ll see the result:

Macro result

Woohoo!

Congratulations on your first Google Sheets macro! You see, it was easy!

Here’s a quick GIF showing the macro recording process in full:

Recording a macro

And here’s what it looks like when you run it:

Run your macro

^ Back to Contents

4. Other options

4.1 Macro Shortcuts

This is an optional feature when you save your macro in Google Sheets. They can also be added later via the Tools > Macros > Manage macros menu.

Shortcuts allow you to run your macros by pressing the specific combination of keys you’ve set, which saves you further time by not having to click through the menus.

Any macro shortcut keys must be unique and you’re limited to a maximum of 10 macro shortcut keys per Google Sheet.

Macro shortcut

In the above example, I could run this macro by pressing:

⌘ + option + shift + 1

keys at the same time (takes practice ?). Will be a different key combo on PC/Chromebooks.

4.2 Deleting macros

You can remove Google Sheets macros from your Sheet through the manage macros menu: Tools > Macros > Manage macros

Under the list of your macros, find the one you want to delete. Click the three vertical dots on right side of macro and then choose Remove macro:

remove macro

4.3 Importing other macros

Lastly, you can add any functions you’ve created in your Apps Script file to the Macro menu, so you can run them without having to go to the script editor window. This is a more advanced option for users who are more comfortable with writing Apps Script code.

import function to macro menu

This option is only available if you have functions in your Apps Script file that are not already in the macro menu. Otherwise it will be greyed out.

^ Back to Contents

5. Best Practices for Google Sheets Macros

Use the minimum number of actions you can when you record your macros to keep them as performant as possible.

For macros that make changes to a single cell, you can apply those same changes to a range of cells by highlighting the range first and then running the macro. So it’s often not necessary to highlight entire ranges when you’re recording your macros.

^ Back to Contents

6. Limitations of Google Sheets Macros

Macros are bound to the Google Sheet in which they’re created and can’t be used outside of that Sheet. Similarly, macros written in standalone Apps Script files are simply ignored.

Macros are not available for other Google Workspace tools like Google Docs, Slides, etc. (At least, not yet.)

You can’t distribute macros as libraries or define them in Sheets Add-ons. I hope the distribution of macros is improved in the future, so you can create a catalog of macros that is available across any Sheets in your Drive folder.

^ Back to Contents

7. A peek under the hood of Google Sheets Macros

Behind the scenes, macros in Google Sheets converts your actions into Apps Script code, which is just a version of Javascript run in the Google Cloud.

If you’re new to Apps Script, you may want to check out my Google Apps Script: A Beginner’s Guide.

If you want to take a look at this code, you can see it by opening the script editor (Tools > Script editor or Tools > Macros > Manage macros).

You’ll see an Apps Script file with code similar to this:

/** @OnlyCurrentDoc */

function FormatText() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setFontWeight('bold')
  .setFontStyle('italic')
  .setFontColor('#ff0000')
  .setFontSize(18)
  .setFontFamily('Montserrat');
};

Essentially, this code grabs the spreadsheet and then grabs the active range of cells I’ve selected.

The macro then makes this selection bold (line 5), italic (line 6), red (line 7, specified as a hex color), font size 18 (line 8), and finally changes the font family to Montserrat (line 9).

The video at the top of this page goes into a lot more detail about this Apps Script, what it means and how to modify it.

Macros in Google Sheets are a great first step into the world of Apps Script, so I’d encourage you to open up the editor for your different macros and check out what they look like.

(In case you’re wondering, the line /** @OnlyCurrentDoc */ ensures that the authorization procedure only asks for access to the current file where your macro lives.)

^ Back to Contents

8. Examples of Google Sheets Macros

8.1 Formatting tables

Record the steps as you format your reporting tables, so that you can quickly apply those same formatting steps to other tables. You’ll want to use Relative references so that you can apply the formatting wherever your table range is (if you used absolute then it will always apply the formatting to the same range of cells).

Check out the video at the top of the page to see this example in detail, including how to modify the Apps Script code to adjust for different sized tables.

8.2 Creating charts

If you find yourself creating the same chart over and over, say for new datasets each week, then maybe it’s time to encapsulate that in a macro.

Record your steps as you create the chart your first time so you have it for future use.

The video at the top of the page shows an example in detail.


The following macros are intended to be copied into your Script Editor and then imported to the macro menu and run from there.

8.3 Convert all formulas to values on current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert any formulas in the current sheet to values.

8.4 Convert all formulas to values in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// convert all formulas to values in every sheet of the Google Sheet
function formulasToValuesGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert all the formulas in every sheet of your Google Sheet into values.

8.5 Sort all your sheets in a Google Sheet alphabetically

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// sort sheets alphabetically
function sortSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = [];
  sheets.forEach(function(sheet,i) {
    sheetNames.push(sheet.getName());
  });
  sheetNames.sort().forEach(function(sheet,i) {
    spreadsheet.getSheetByName(sheet).activate();
    spreadsheet.moveActiveSheet(i + 1);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will sort all your sheets in a Google Sheet alphabetically.

8.6 Unhide all rows and columns in the current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// unhide all rows and columns in current Sheet data range
function unhideRowsColumnsActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  sheet.unhideRow(range);
  sheet.unhideColumn(range);
}

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range. (If you have hidden rows/columns outside of the data range, they will not be affected.)

8.7 Unhide all rows and columns in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// unhide all rows and columns in data ranges of entire Google Sheet
function unhideRowsColumnsGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    sheet.unhideRow(range);
    sheet.unhideColumn(range);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range in each sheet of your entire Google Sheet.

8.8 Set all Sheets to have a specific tab color

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// set all Sheets tabs to red
function setTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor("ff0000");
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will set all of the tab colors to red.

Want a different color? Just change the hex code on line 5 to whatever you want, e.g. cornflower blue would be 6495ed

Use this handy guide to find the hex values you want.

8.9 Remove any tab coloring from all Sheets

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// remove all Sheets tabs color
function resetTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor(null);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove all of the tab colors from your Sheet (it sets them back to null, i.e. no value).

Here’s a GIF showing the tab colors being added and removed via Macros (check the bottom of the image):

color tabs with Macros

8.10 Hide all sheets apart from the active one

Copy and paste this code into your script editor and import the function into your Macro menu:

function hideAllSheetsExceptActive() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName()) 
      sheet.hideSheet();
  });
};

Running this macro will hide all the Sheets in your Google Sheet, except for the one you have selected (the active sheet).

8.11 Unhide all Sheets in your Sheet in one go

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

function unhideAllSheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.showSheet();
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will show any hidden Sheets in your Sheet, to save you having to do it 1-by-1.

Here’s a GIF showing how the hide and unhide macros work:

hide unhide sheets with macros

You can see how Sheet6, the active Sheet, is the only one that isn’t hidden when the first macro is run.

8.12 Resetting Filters

Ok, saving the best to last, this is one of my favorite macros! 🙂

I use filters on my data tables all the time, and find it mildly annoying that there’s no way to clear all your filters in one go. You have to manually reset each filter in turn (time consuming, and sometimes hard to see which columns have filters when you have really big datasets) OR you can completely remove the filter and re-add from the menu.

Let’s create a macro in Google Sheets to do that! Then we can be super efficient by running it with a single menu click or even better, from a shortcut.

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// reset all filters for a data range on current Sheet
function resetFilter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.getFilter().remove();
  range.createFilter();
}

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove and then re-add filters to your data range in one go.

Here’s a GIF showing the problem and macro solution:

Macro to reset filters

^ Back to Contents

9. Resources

If you’re interested in taking things further, check out the following resources for getting started with Apps Script:

Macro reference guide in Google Docs help

Macro reference guide in the Google Developer documentation

And if you want to really start digging into the Apps Script code, you’ll want to bookmark the Google documentation for the Spreadsheet Service.

Finally, all of this macro code is available here on GitHub.

How To Connect Google Sheets To A Database, Using Apps Script

This is a guest post from Mike Ritchie, co-founder of Seekwell.io, which adds SQL to the apps you need it in.

Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.

In this post we cover a few ways to get data from your SQL database into Google Sheets.

Google Sheets Database Connection With Apps Script

Sheets comes with a built-in app development platform called “Apps Script”.

Based on JavaScript, it covers a lot of the tasks you’d use VBA for in Excel.

App Script comes with a JDBC Service that lets you connect to MySQL, Microsoft SQL Server, and Oracle databases.

Steps To Connect Google Sheets To A Database

1) Open the Script Editor in Sheets using “Tools” → “Script editor”. Or just copy this Sheet here.

How To Connect Google Sheets To A Database
Access Apps Script under the menu Tools > Script editor

2) Replace “Code.gs” with the code here. (Skip this if you copied the Sheet above)

3) We included credentials for SeekWell’s demo MySQL database. To connect to your database, replace the six fields below. Note you’ll need to whitelist Google’s IP addresses.

var HOST = 'yourhostname'
var PORT = '3306 or your port'
var USERNAME = 'yourusername'
var PASSWORD = 'yourpassword'
var DATABASE = 'youdatabasename'
var DB_TYPE = 'mysql or your type'

4) You might also want to change the MAXROWS, but you don’t go too crazy, Sheets has a hard limit of 10 million cells and the query will take longer to run with more rows.

5) Save the file and refresh / refresh the Sheet. You’ll see a new menu option of “SeekWell Lite” show up.

6) The script is set up to read the query from query!A2 and write the results to your active cell, so you’ll need to add a sheet called “query” and add the query below in the cell query!A2 (skip if you copied the Sheet above).

SELECT *
FROM dummy.users
LIMIT 100

7) Go back to Sheet1, click in cell C4 (or any other cell) and click “SeekWell Lite” → “Run SQL”.

In a few moments you’ll see the data show up!

A few problems with this approach

You need to store your password in plain text in the Code.gs file.

Sharing the script with your team and adding the script to different Sheets is a bit of a pain. You can publish an addon, but that comes with some overhead.

And scheduling / automating refreshes can be cumbersome when you need many different queries going to many different Sheets.

Google’s JDBC service doesn’t work for Postgres, Snowflake or RedShift and requires a long list of whitelisted IP’s. It also doesn’t support SSH.

Alternatives to Google Sheets database connections with App Script

Python

If you’re comfortable with Python, you can put together a program using Pandas and the Sheets API. Pandas has great SQL support built in.

SaaS Products

A lot of people hate paying for things they can do for free, but you should always do some napkin math when making the “build vs. buy” decision.

In the case of automating reports, the ROI can be pretty high, especially if you have several daily, hourly, or near real time dashboards you need to keep updated. ActionDesk did a good overview of the options out there.


This is a guest post written by Mike Ritchie. Mike is the co-founder of Seekwell and has over 15 years experience in analytics.

SeekWell features include:

  • Takes < 2 minutes to get your first schedule set up
  • A shared code repository with every query anyone on your team has ever written
  • Beautiful query editor with autocomplete and snippets
  • Ability to automate alerts via Slack and email
  • Support for MySQL, Postgres, Snowflake, Redshift, Salesforce, and SQL Server