Streamline Your Workflow with Google Apps Script: Managing Files and Cleaning Docs Effortlessly

by

Google Apps Script is a powerful tool for automating tasks in Google Workspace. In this post, we’ll explore a script that integrates seamlessly with Google Sheets, helping you manage files in Google Drive and clean up Google Docs with ease.

Features of the Script

This script offers three main functionalities:

  1. Custom Menu Integration: Adds a menu to Google Sheets for easy access.
  2. List Files in a Folder: Fetches file details (name, ID, and path) from a Google Drive folder and lists them in a Google Sheet.
  3. Clean and Reformat Docs: Opens a Google Doc, removes unnecessary content, and resets formatting.

How It Works

1. Adding a Custom Menu

The script’s onOpen function adds a menu called “Doc Tools” to your Google Sheet. This menu provides quick access to the script’s main features.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Doc Tools")
    .addItem("Clean and Reformat Doc", "cleanAndReformatDoc")
    .addToUi();
}

When the spreadsheet is opened, the menu is automatically added, allowing users to interact with the script directly from the Google Sheets UI.

2. Listing Files in a Google Drive Folder

The listFilesInFolder function fetches details of all files in a specified folder and lists them in the active Google Sheet. It includes:

  • File Name
  • File ID
  • Full Path
function listFilesInFolder() {
  const folderId = "YOUR_FOLDER_ID"; // Replace with your Folder ID
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFiles();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet.clear();
  sheet.appendRow(["File Name", "File ID", "File Path"]);

  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    const fileId = file.getId();
    const filePath = getFullPath(file);

    sheet.appendRow([fileName, fileId, filePath]);
  }
}

function getFullPath(file) {
  let currentFolder = file.getParents().next();
  let path = currentFolder.getName();

  while (currentFolder.getParents().hasNext()) {
    currentFolder = currentFolder.getParents().next();
    path = currentFolder.getName() + " > " + path;
  }

  return path;
}

This feature is particularly useful for quickly auditing or managing files in a Google Drive folder.

3. Cleaning and Reformatting Google Docs

The cleanAndReformatDoc function takes a Google Doc ID from the selected cell in the sheet, opens the document, and applies the following cleaning actions:

  • Remove Empty Lines: Deletes unnecessary blank lines while preserving the last paragraph.
  • Remove Specific Patterns: Deletes “Copy code” lines followed by specific words like “html”, “javascript”, or “css”.
  • Reset Font Size: Resets all text in the document to the default font size.
Remove Empty Lines
function removeEmptyLines(body) {
  const paragraphs = body.getParagraphs();
  for (let i = paragraphs.length - 1; i >= 0; i--) {
    const paragraph = paragraphs[i];
    const text = paragraph.getText().trim();
    if (i === paragraphs.length - 1 && body.getChildIndex(paragraph) === body.getNumChildren() - 1) {
      continue;
    }
    if (text === "") {
      body.removeChild(paragraph);
    }
  }
}
Remove Patterns
function removeCopyCodePatterns(body) {
  const paragraphs = body.getParagraphs();
  const pattern = /^Copy code\s*(html|javascript|css)$/i;

  for (let i = paragraphs.length - 1; i > 0; i--) {
    const currentText = paragraphs[i].getText().trim();
    const previousText = paragraphs[i - 1].getText().trim();

    if (pattern.test(previousText) && currentText.toLowerCase() === pattern.exec(previousText)[1]) {
      body.removeChild(paragraphs[i]);
      body.removeChild(paragraphs[i - 1]);
      i--;
    }
  }
}
Reset Font Size
function resetFontSize(body) {
  const paragraphs = body.getParagraphs();
  for (const paragraph of paragraphs) {
    const text = paragraph.editAsText();
    text.setFontSize(null);
  }
}

Using the Script

Setup

  1. Open your Google Sheet and click Extensions > Apps Script.
  2. Paste the script into the editor and save it.
  3. Reload your Google Sheet to see the “Doc Tools” menu.

Usage

  1. Select “Clean and Reformat Doc” to clean a Google Doc. Ensure the selected cell contains a valid Doc ID.
  2. Run “List Files in Folder” to populate the active sheet with file details from a specific Google Drive folder.

Benefits

  • Automated Workflow: Streamline tedious file management and document cleaning tasks.
  • Seamless Integration: Leverage Google Sheets as a control hub for file and document operations.
  • Customizable: Extend the script to include additional cleaning or management features.

Conclusion

This Google Apps Script demonstrates the power of automation in Google Workspace. Whether you’re managing files in Google Drive or cleaning up messy Google Docs, this script offers a simple yet effective solution.

Feel free to customize the script to suit your specific needs, and let us know how it transforms your workflow! 🚀