Introduction to Google Script Editor: Automate Your Data Analysis in Google Sheets

Image from Author

Background

A couple of months ago (when I wrote this article in June 2023), I was asked to help several colleagues at the company where I work in automating their data processes. Additionally, I have been invited to speak at webinars, campus events, and other online/offline gatherings. The questions I have received are quite similar:

• How can I automate the process of removing duplicate data without manual work?
• If I have more than two sheets and want to compile them into one, how can I automate the process?
• I have fetched data from external sources, how can I retrieve the previous performance (marketing metrics) on a daily basis?

It is fascinating to see that many individuals are using spreadsheets to automate the process, allowing them to focus on analyzing the data instead of spending a significant amount of time and energy on manual data entry.

The purpose of this article is to provide you with an understanding of Google Script and its application for data analysis purposes.

What is Google Script Editor?

The Google Script Editor is a development environment provided by Google for creating custom scripts and automating tasks within various Google Applications, such as Google Sheets, Google Docs, and Google Forms. It allows users to extend the functionality of these applications by writing code based on JavaScript.

Image from Author

In order to access the Google Script Editor in the Google Sheets, you can go to the “Extension” menu, and select “Apps Script”. This will open the Script Editor in a new window or tab.

Image from Author

Basic Script in Google Script Editor

For data analysis purposes, I utilize the Google Script Editor to access and manipulate the specific range of rows and columns in Google Sheets. The following :

• The `getSheetByName()` function is used to select the desired sheet for processing.
• The `getRange()` function retrieves data from the specified range of rows and columns.
• The `getLastRow()` function fetches data up to the last row in the spreadsheet.
• The `getLastColumn()` function retrieves data up to the last column in the spreadsheet.
• The `getValues()` function retrieves the value from the processed operation.
• The `setValues()` function is typically used to set values based on the output of `getValues()` or a custom formula

You can imagine how a script editor works by putting yourself to write the script

These functions provide crucial capabilities for accessing, manipulating, and analyzing targeted data in Google Sheets. By utilizing the Google Script Editor, data analysts can streamline their workflows and perform advanced data operations efficiently.

Moving data from one sheet to another sheet

I want you to get your hands dirty by examining the script editor in your Google Sheets. At this part, you will move all data from one sheet to another sheet in the same Google Sheets document.

Image from Author

function movingData() {
  //1. Get active sheets that you want to process
  var sheetOne = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheetTwo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  //2. Get the data from the selected range of cell and get the values
  var sheetOneValue = sheetOne.getRange(1,1,sheetOne.getLastRow(), sheetOne.getLastColumn()).getValues();

  //3. Eliminate the blank rows
  var val = sheetOneValue.filter(e=>e[0]); 
  
  //4. From step two and three, put the values in the beginning of the first row and first column in Sheet 2
  sheetTwo.getRange(1,1, val.length, val[0].length).setValues(sheetOneValue);
}

From the provided code, let me explain the step-by-step process of moving data from one sheet to another sheet.

1. The variables sheetOneand sheetTwo are used to select the active sheet names, in this case, “Sheet1” and “Sheet2”
2. The variable sheetOneValue retrieves the values from the specified range of your cells in Sheet1. The code `(1, 1, sheetOne.getLastRow(), sheetOne.getLastColumn())` represents the range of cells from the first row and first column to the last row and last column.
3. Step three involves counting the number of filled columns and rows in Sheet1. This step is also used to eliminate any blank rows.
4. Finally, the data from Sheet1 will be populated into Sheet2, starting from the first row and columns. The number of columns and rows will be based on the total count of filled data in Sheet1.

Formula implementation in Google Script Editor

I often receive various requests from my colleagues, including tasks like filtering data based on specific categories or aggregating data. For such requests, the “Query” function in Google Sheets can be a straightforward solution.

However, what if the scenario involves combining data from multiple sheets into one and filtering out individuals below the age of 30?

Image by Author

I provide you the view access to Google Sheets to combine several sheets into one sheet and aggregate to filter out the age below 15 years old.

function movingData() {
  //1. Get active sheets that you want to process
  var sheetOne = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("exampleSheet1");
  var sheetTwo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("exampleSheet2");
  var sheetThree = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("exampleSheet3");
  var sheetCompiled = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("compiledSheet");

  //2. Get the data from the selected range of cell and get the values
  var sheetOneValue = sheetOne.getRange(1,1,sheetOne.getLastRow(), sheetOne.getLastColumn()).getValues();
  var sheetTwoValue = sheetTwo.getRange(2,1,sheetTwo.getLastRow(), sheetTwo.getLastColumn()).getValues();
  var sheetThreeValue = sheetThree.getRange(2,1,sheetThree.getLastRow(), sheetThree.getLastColumn()).getValues();

  //3. Get rid of blank rows
  var valOne = sheetOneValue.filter(e=>e[0]);
  var valTwo = sheetTwoValue.filter(e=>e[0]); 
  var valThree = sheetThreeValue.filter(e=>e[0]); 

  //4. Clear the data in the compiled sheet
  sheetCompiled.getRange(1, 1, sheetCompiled.getLastRow(), sheetCompiled.getLastColumn()).clearContent();

  //5. Compile all data points in each sheet into compiledSheet
  sheetCompiled.getRange(1, 1, valOne.length, valOne[0].length).setValues(sheetOneValue);
  sheetCompiled.getRange(sheetCompiled.getLastRow()+1, 1, valTwo.length+1, valTwo[0].length).setValues(sheetTwoValue);
  sheetCompiled.getRange(sheetCompiled.getLastRow()+1, 1, valThree.length+1, valThree[0].length).setValues(sheetThreeValue)

  //6. Get the header of the data
  var header = sheetCompiled.getRange(1,1,1,sheetCompiled.getLastColumn()).getValues();
  
  //7. Put the header into another cell and implement aggregation to filter out age above 30
  sheetCompiled.getRange(1, 12, 1, 9).setValues(header);
  sheetCompiled.getRange(2, 12).setValue('=QUERY(A2:I67,"SELECT * WHERE A > 30")');
 
}

The code is quite similar to the previous example to copy and paste from one sheet to another sheet. Additionally, I use a formula to query all data points with a filter of the age below 30 years old.

Scheduling your automation

In the Google Script Editor, you can schedule your script to automate data analysis tasks using “Trigger”. A Trigger is a special function that allows your script to run automatically based on certain events or time intervals.

In cases where you need to compile data daily or schedule regular data inputs, I find triggers particularly useful. You can set them to run on a monthly, weekly, or daily basis, or even at intervals as short as minutes. Triggers are flexible and can be adjusted to meet your specific requirements for task automation.

Image from Author

Conclusion

When I first started using Google Script Editor, I was amazed at how much time I saved. Tasks that used to take me around 4 hours to compile, process, and aggregate data could now be automated in just 5 seconds using Google Sheets as the primary tool for data analysis.

The Script Editor is truly remarkable, and I have utilized it in various cases, including product analysis, marketing tasks, operational processes, and growth analysis. It allows me to transform every manual task into automation. Spreadsheets, particularly Google Sheets, have always been my go-to for data handling and analysis. So, why not leverage the power of Google Script Editor?

I offer a free 15-minute discussion to understand your pain points in using Google Spreadsheet and to help you automate your day-to-day work using Google Sheets. Please follow me on Medium or LinkedIn, I am happy to share insightful articles that will make your life easier and more productive.

Thanks for reading!