Google Sheets can do a lot—but with Apps Script, it does everything. I’d been using Sheets for years without touching this feature. Once I did, I started automating the chores I didn’t even realize were slowing me down.
Automating Boring Tasks
If you’re tired of repeating the same task over and over again in Google Sheets, whether it’s inserting a date, cleaning up text, applying formatting, or exporting data, Apps Script can help you automate that.
For instance, you can easily insert the current date in Google Sheets by pressing Ctrl + ; on Windows or Cmd + ; on Mac. But if you need it to be in a particular format like yyyy-MM-dd, you must use a formula or do some manual formatting. With Apps Script, you can create a function that gets the current date, formats it as desired, and then inserts it into the selected cells.
You only need to do this once, and you can reuse it wherever you need it. Here is an example of what the script would look like:
function insertCurrentDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var selectedCell = sheet.getActiveCell();
var currentDate = new Date();
var formattedDate = Utilities.formatDate(currentDate, Session.getScriptTimeZone(),'yyyy-MM-dd');
selectedCell.setValue(formattedDate);
}
If you are following along, I will show you how to run the above code from within a spreadsheet in the next section.
Create Customized Spreadsheets
One way Apps Script helps you customize your spreadsheet is by letting you create specialized tools that streamline your workflow. For instance, you can insert a button that applies formatting or performs advanced calculations, or create dashboards that help you to easily analyze your data. These capabilities are far beyond anything Google Sheets can do on its own.
Continuing with our example from the previous section, we can add a custom menu item that allows us to run the function from within the spreadsheet. Here is the script that does that (insert above the function in the previous section):
function onOpen(){
var ui = SpreadsheetApp.getUi();
ui.createMenu('My Menu').addItem('Insert Current Date', 'insertCurrentDate').addToUi();
}
Once you run this code in Apps Script and refresh Google Sheets, you will see My Menu appear in the top menu. Now, you can insert the current date in a selected cell by clicking My Menu > Insert Current Date.
Perform Specific Calculations Through Custom Functions
Google Sheets doesn’t always have the functions you need to perform specific calculations. In cases like these, creating your own function using Apps Script, inserting the calculation into it, and using it within the sheet (just like you would any other) is the way to go. This also means that you don’t have to repeat the complex calculations throughout the script, making your spreadsheet cleaner and more readable.
Related
30+ Essential Google Sheets Functions
Manipulate your Google Sheets data and simplify processes with these handy functions, available in a free downloadable cheat sheet.
Consider the function below that accepts a date and checks how many days have passed since the current day (similarly to calculating the difference between two dates) to determine the due date:
function CHECKDUEDATE(inputDate) {
var today = new Date();
var timeDiff = today - inputDate;
var daysDiff = Math.floor(timeDiff / (1000 * 60 * 60 * 24)); if (daysDiff < 0){
return "Overdue!"
} else if (daysDiff > 0) {
return "Due in " + daysDiff.toString() + " days!"
} else {
return "Due today!"
}
}
Depending on the date entered, it says if it’s overdue, if the days difference is less than zero, how many days are left if greater than zero, or due that day if equal to zero.
The best part of Apps Script is that you can create a time-based trigger that runs the function at a specific time (e.g., midnight) to dynamically update the due date. It can even be used for conditional formatting—the possibilities are many.
You Can Use Apps Script with Other Google Services
Apps Script allows you to integrate your spreadsheets with other Google services, including Gmail, Google Drive, and Google Docs. This capability enables you to build comprehensive workflows that span multiple tools in the Google ecosystem.
Here is an example script that extracts the body text from a Google Docs document and inserts it into cell A1:
function getBodyTextFromGoogleDoc() {
const doc = DocumentApp.openById("insert Google Docs ID here");
const bodyText = doc.getBody().getText(); const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const targetRange = sheet.getRange("A1");
targetRange.setValue(bodyText);
}
Be sure to replace insert Google Docs ID here in the second line with the ID of the Google Docs document you want to get the body text from (don’t remove the parentheses).
Apps Script Is Not That Hard to Learn
If you have some basic programming knowledge, Apps Script is particularly easy to learn. This is especially true if you’re familiar with JavaScript, as that is the primary language used by the platform. You can start slowly with simple automations and tasks (basic calculations and sending an email), and build up from there.
Even if you’ve never written code in your life, Apps Script can be a great way to learn. Since you’ll mainly be using it to automate tasks in Google Sheets, you will see results instantly, which is a great motivator. With that said, you still need to learn basic programming concepts (e.g., variables, loops, and functions)—Google has great tutorials, and Apps Script has a supportive community behind it to assist you.
Furthermore, there is no need for a complicated setup, as it runs in the cloud. You don’t need additional software or libraries.
Related
These Free Websites Taught Me More Than Any Paid Course Ever Did
Paid doesn’t mean better.
Apps Script is just one of the many Google Docs features that you’re missing out on and worth a try. The cool thing about Apps Script is that you can also use it in other Google Workspace apps like Docs and Slides. For instance, I created a simple text case converter in Google Docs since it doesn’t have that functionality built-in.
