Increase coding productivity with Apps Script macros

Google Apps Script is all about increasing work productivity through automation. But the road to automation involves a lot of mental and manual work. When we're developing scripts, we are often asked to make changes in Google Sheets or Google Drive items. Since we don't always get the scripts to make the right changes right away, we often have to undo the script changes manually, as we develop and enhance the script. And there's only so much we can do with the UNDO button in google sheets.

Let me show you how I use macros in Apps Script to cut down on the manual undoing, and make my work as a developer more efficient. I'll be covering four areas of changes:

• Adding content to a google sheet
• Changing the format of cells in a sheet
• Adding new tabs to a sheet
• Adding new files to a Google Drive

I'll show you how to write the macros and how to include them in Google Sheet.

I've set up a Google Drive folder with a sub-folder and a Google Sheet. Open the Google Sheet and then the Apps Script editor. We'll leave Code.gs empty – you'll use that for the actual automation. Create a new script file called "Macros.gs" where we will write our macros code. We will use the sub-folder later to undo file additions into the sub-folder.

Let's write the code for our first macro: undoing the addition of content into a Google Sheet:

function deleteData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.clearContents();
  SpreadsheetApp.flush();
}

In the code above, we get the active Google Sheet and its active sheet. We use clearContents() to clear the contents (but not the formatting) of the active sheet. We then flush the pending changes.

In order to register the function as a macro, we need to add some content to our manifest file. Open the "appsscript.json" file. If the file isn't available in the file list then head to the Project Settings window by click on the gear icon on the left side. Tick the box 'Show "appsscript.json" manifest file in editor.' Add the following code:

{
  "timeZone": "...",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "sheets": {
    "macros": [
      {
        "menuName": "Delete content",
        "functionName": "deleteData",
        "defaultShortcut": "Ctrl+Alt+Shift+1"
      }
    ]
  }
}

Essentially, we're adding a new "sheets" section to our file, which is an object containing "macros". The latter points to an array of object, each of which represents one macro. You define the name of the macro in the menu, the function to run, and the optional keyboard shortcut. Once you save the file, the macro should appear inside the Extensions > Macros menu.

I've had situations where my automation inserted new rows to the sheet. I wanted to be able to delete the inserted rows instead of deleting their content. In that case, use the following code:

function deleteData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.clearContents();
  sh.deleteRows(2, sh.getMaxRows() - 1);
  SpreadsheetApp.flush();
}

Undoing formatting changes is super easy. The function looks similar to the first one with just a different method clearFormats being used:

function deleteData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.clearFormats();
  SpreadsheetApp.flush();
}

Another useful undo macro is the ability to remove any tab that's to the right of the active tab. Again, I've written several automation projects that inserted new sheets to a Google Sheet. In order to remove them with code, use:

function deleteTabs() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const activeName = sh.getName();
  const sheets = ss.getSheets();
  const activePosition = sheets.findIndex(
    (sheet) => sheet.getName() === activeName
  );
  for (let i = sheets.length - 1; i > activePosition; i--) {
    ss.deleteSheet(sheets[i]);
  }
}

Above, we get the name of the active sheet, and the collection of sheets. We iterate over the collection, starting at the end, and delete any sheet, until we arrive at the active sheet. This is super helpful in situations where you have tabs to the left of the active sheet that you don't want to delete. Only tabs to the right of the active sheet will be deleted.

For the last macro, we need the ID of the subfolder. Open it and copy its ID from the end of its URL in the address bar. Then add it to the following code:

function deleteFiles() {
  const folderId = 'your-folder-id';
  const fileRefs = DriveApp.searchFiles(`"${folderId}" in parents`);
  while (fileRefs.hasNext()) {
    const file = fileRefs.next();
    file.setTrashed(true);
  }
}

Above, we search Google Drive for all files that reside in the subfolder. We get each file and set it to trashed. Google Drive will move it to the Trash automatically.

The final version of the manifest file, containing all four macro declarations:

{
  "timeZone": "",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "sheets": {
    "macros": [
      {
        "menuName": "Delete content",
        "functionName": "deleteData",
        "defaultShortcut": "Ctrl+Alt+Shift+1"
      },
      {
        "menuName": "Clear formats",
        "functionName": "clearFormats",
        "defaultShortcut": "Ctrl+Alt+Shift+2"
      },
      {
        "menuName": "Delete tabs",
        "functionName": "deleteTabs",
        "defaultShortcut": "Ctrl+Alt+Shift+3"
      },
      {
        "menuName": "Delete files",
        "functionName": "deleteFiles",
        "defaultShortcut": "Ctrl+Alt+Shift+4"
      }
    ]
  }
}

I hope this helps increase your productivity. Happy GAS coding!