Work faster in Google Docs and Sheets by creating your own shortcuts

Enhance Google Docs and Sheets Productivity with Scripts and Macros

This article has been updated since its original publication on November 26, 2018.

Google Drive offers more than cloud storage; it provides a powerful office suite including Docs and Sheets. While these web apps are designed for speed and ease of use, their functionality can be significantly boosted with scripts and macros.

JavaScript-based scripts add extra features. Imagine automating invoice generation in Sheets or rapidly converting Docs files to PDFs.

Macros are specialized scripts—sequences of actions triggered by a single keystroke or click. They streamline repetitive tasks, such as applying consistent formatting to large text blocks or duplicating spreadsheet rows. Mastering scripts and macros unlocks significant productivity gains.

Adding Scripts to Google Apps

Proficient JavaScript users can write their own scripts. In Docs or Sheets, navigate to Extensions > Apps Script. This opens the Apps Script editor where you can create new scripts or execute existing ones (using the Run button). Even without coding skills, you can leverage pre-built scripts.

Numerous Google app add-ons are available online. Some provide complete installation instructions, others require copying and pasting code into the script editor. Here are a few useful examples:

• Mail Merge: Populates Gmail drafts with data from a Google Sheet. Google provides guidance, but you can customize the code (e.g., adding BCC/CC recipients).
• Website Monitor: Logs website uptime/downtime, tracking load times and detecting outages. The script is embedded in a Google Sheet; simply adjust parameters (like the URL) and run.
• Invoice Generator (SheetGo): Transforms Google Sheet rows into invoices. Enter data (company details, payment amounts) into the sheet, and the script generates a PDF invoice. The code is relatively straightforward, making customization easy.

After importing scripts, manage them via the Apps Script web portal. The information icon (a circled "i") displays usage frequency. To modify a script, click on it. Always back up scripts before editing by selecting the file options (three dots) and choosing Make a copy.

While learning to code your own scripts takes time, resources like this Zapier blog post offer valuable guidance for beginners.

Creating Custom Shortcuts with Macros

Macros extend script functionality. Recording a macro generates a script automating repetitive actions, creating a mini-application for time-consuming tasks.

Currently, macros are available in Sheets but not yet in Docs or Slides. They're a long-standing feature in Microsoft Office (Word, Excel), automating formatting, data entry, and other repeated actions.

To create a Sheets macro, open a spreadsheet and go to Extensions > Macros > Record macro. The recording starts immediately. Perform your task as usual, then click Save to store it or Cancel to discard it.

During recording, choose between Use absolute references (macro runs from the same location) and Use relative references (macro runs relative to the currently selected cell). For example, use absolute references to consistently format the top row; use relative references to format cells relative to the selection.

After saving, name the macro and assign a keyboard shortcut (optional). Access and manage macros under Extensions > Macros. The Manage macros option allows renaming, re-assigning shortcuts, deleting, or viewing the code in the Apps Script editor. Examining the code helps understand the actions performed.

Google requires permission before running a macro due to potential security risks. Always verify the macro's origin before granting permission.

The above is the detailed content of Work faster in Google Docs and Sheets by creating your own shortcuts. For more information, please follow other related articles on the PHP Chinese website!