Apps Script for Google Sheets Is the Productivity Solution You've

This guide organizes the entries in "Apps Script for Google Sheets Is the Productivity Solution You've" and explains what each option, idea, or example offers. It also highlights practical details that matter when comparing the choices.

Automate boring tasks

If you're tired of doing the same tasks over and over again in Google Sheets, whether it's inserting dates, cleaning up text, applying formatting, or exporting data, Apps Script can help you automate it.

For example, you can easily insert the current date into Google Sheets by pressing Ctrl +; on Windows or Cmd +; on Mac. But if you need the current date in a specific format like yyyy-MM-dd, you have to use a formula or do some manual formatting. With Apps Script, you can create a function that takes the current date, formats it however you want, and then inserts it into selected cells.

You only need to do this once and can reuse it wherever you need it. Here's an example of what the script looks 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); }

The article will guide you how to run the above code from inside the spreadsheet in the next section.

Create custom spreadsheet

One way Apps Script helps you customize your spreadsheet is by allowing you to create specialized tools that streamline your workflow. For example, you can insert a button that applies formatting or performs advanced calculations, or create a dashboard that makes it easy to analyze your data. These capabilities go beyond anything Google Sheets can do on its own.

Continuing with the example from the previous section, we can add a custom menu item that allows us to run a function from within the spreadsheet. Here is the script that does this (inserted above the function from the previous section):

function onOpen(){ var ui = SpreadsheetApp.getUi(); ui.createMenu('My Menu').addItem('Insert Current Date', 'insertCurrentDate').addToUi(); }

After running this code in Apps Script and refreshing Google Sheets, you should see My Menu appear in the top menu. You can now insert the current date into the 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 this, it's best to create your own function using Apps Script, insert the calculation into it, and use it in your spreadsheet (just like any other function). This also means you don't have to repeat complex calculations throughout your script, keeping your spreadsheet cleaner and easier to read.

Consider the function below, which accepts a date and checks the number of days elapsed since the current date (similar 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 will indicate whether it is overdue, if the date difference is less than 0, how many days are left if greater than 0, or the due date if zero.

The biggest advantage of Apps Script is that you can create a time-based trigger to run a function at a specific time (e.g. midnight) to dynamically update the due date. It can even be used for conditional formatting - there are so many possibilities.

You can use Apps Script with other Google services

Apps Script lets you integrate spreadsheets with other Google services, including Gmail, Google Drive, and Google Docs. This capability lets you build comprehensive workflows that span multiple tools in the Google ecosystem.

Here is an example of a 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); }

Remember to replace "insert Google Docs ID here" in the second line with the ID of the Google Docs document you want to get the main content from (don't remove the parentheses).