I Automated My Entire Google Drive With n8n – It Organizes Itself

Key Concepts

• NN Workflow: An automated system for organizing Google Drive files.
• MIME Type: A standard for identifying the type of file (e.g., application/pdf for PDF documents).
• Google Cloud Console: A platform for managing Google Cloud services, including enabling APIs and creating credentials.
• OAuth Consent Screen: A screen in Google Cloud Console where you configure permissions for your application to access Google services.
• OpenAI API: An interface for accessing OpenAI's AI models, such as GPT-4, for tasks like text extraction and categorization.
• JSON (JavaScript Object Notation): A lightweight data-interchange format used for structuring data.
• Substring: A portion of a string (text) extracted from a larger string.
• Switch Statement: A control flow statement that allows you to execute different code blocks based on the value of a variable.
• Fallback: A default action or path taken when no other conditions are met.

Google Drive Organization with NN Workflow

Introduction

The video demonstrates how to automatically organize a Google Drive account using an NN workflow. The goal is to eliminate the anxiety caused by disorganized files by intelligently sorting them into appropriate folders. The workflow reads file types (MP3, Google Sheets, Docs, PDFs, etc.) and determines their correct folder placement.

Demo of the Workflow

The presenter demonstrates the workflow using a Google Sheet containing frequently asked questions from their school community. The workflow identifies the file as a Google Sheet, downloads it, extracts the text, and determines that it belongs in the "community" folder. It then moves the file, renames it for organization, and places it in the designated folder. A Google Sheet is used to track automatically sorted files, allowing for manual correction if needed.

Setting up the Workflow in NN

The video focuses on building two paths within the workflow: one for processing documents (Sheets, Docs, Slides) and another for Google Forms. The presenter mentions that the blueprints are available for free download.

Creating a New Scenario

1. Create a new scenario in NN.
2. Add a Google Drive module with the trigger "On changes to a specific folder."

Connecting to Google Drive (Client ID and Client Secret)

This is described as the most tedious connection in NN. The process involves obtaining a client ID and client secret from the Google Cloud Console.

1. Go to the Google Cloud Console.
2. Create a new project (e.g., "Google Drive test").
3. Enable the Google Drive API for the project.
4. Configure the OAuth consent screen:
   • Choose "External" user type.
   • Enter a contact email.
   • Add yourself as a user in the "Audience" section.
   • Publish the app.
5. Create OAuth 2.0 credentials:
   • Choose "Web application" type.
   • Add the authorized redirect URI from NN.
6. Copy the client ID and client secret from the credentials and paste them into the NN Google Drive module.

Configuring the Google Drive Trigger

1. Set the trigger to watch for file creation in the root folder by entering "root" as the folder ID.
2. Select "Watch for file creation."

Processing Different File Types

The workflow handles various file types (audio, Google Docs, Google Sheets, PDFs, videos) with slightly different processing methods. The core logic remains the same, requiring only minor adjustments for each type.

Using an "If" Statement to Determine File Type

An "if" statement is used to determine the file type based on its MIME type. This allows for merging multiple file types (Slides, Sheets, Docs, PDFs) down a single path.

1. Use the MIME type to identify the file type.
2. Use "OR" conditions to check if the MIME type matches Google Docs, Google Sheets, Google Slides, or PDF.
3. Use Chat GPT to determine the most popular MIME types for Google Drive.

Adding a "Wait" Step

A 24-hour wait step is added to allow time for newly created files to be populated with content before processing. This ensures that the workflow has data to determine the file's category.

Downloading the File

Use the Google Drive "Download a File" module to download the file. The file ID is obtained from the trigger data.

Converting Files to PDF

Use the "Google File Conversion" module to convert all files (Google Docs, Sheets, Slides) to PDF format. This ensures consistent processing.

Extracting Text from PDF

Use the "Extract Text from PDF Document" module to extract text from the PDF file. The data from the downloaded file is mapped to this module.

Categorizing Files with OpenAI

The workflow uses OpenAI to categorize files based on their content.

Connecting to OpenAI

1. Create an account on platform.openai.com.
2. Generate an API key.
3. Add the API key to a new credential in the NN OpenAI module.
4. Ensure that the OpenAI account has a sufficient balance (at least $5).

Configuring the OpenAI Chat Model

1. Select a model (e.g., GPT-4 Mini).
2. Add three messages:
   • System Message (Parameters): Provides instructions to the AI, such as "You are an intelligent bot capable of categorizing a PDF file."
   • User Message (Input): Passes the text of the file to the AI. Includes the file's creation date and a substring to limit the text length to 5,000 characters.
   • Assistant Message (Output): Specifies the desired output format (JSON) with keys for category, file name, and file ID.

System Prompt Example

The system prompt instructs the AI to:

• Identify the category the file belongs to (e.g., accounting, recruitment).
• Associate the correct folder ID with the category.
• Rename the file with a descriptor and the creation date.

The prompt includes a list of categories and their corresponding folder IDs.

Ensuring JSON Output

The output from OpenAI is converted to JSON format using the JSON parse function to ensure consistent data structure.

Consolidating Data with "Set Multiple Variables"

A "Set Multiple Variables" module is used to consolidate the file name, file ID, folder ID, and category into a single data structure. This ensures consistent data handling regardless of the path the file takes through the workflow.

Moving and Renaming the File

1. Use the Google Drive "Move a File" module to move the file to the correct folder. The file ID and folder ID are obtained from the consolidated data.
2. Use the Google Drive "Update a File" module to rename the file. The new file name is obtained from the consolidated data.

Tracking File Movements in Google Sheets

The workflow appends a row to a Google Sheet to track the movement of files. This allows for monitoring and manual correction if needed.

1. Use the Google Sheets "Append a Row" module.
2. Create a new connection to Google Sheets.
3. Select the target spreadsheet and sheet.
4. Map the following data to the sheet:
   • Date (using the now function)
   • File name
   • View link (using a specific format with the file ID)
   • Folder link (using a specific format with the folder ID)
   • Category
   • File type (using a custom inline function to convert the MIME type to a user-friendly name)
   • Approval status (a checkbox initialized to "false")

Processing Google Forms

The video briefly covers processing Google Forms.

1. Use a "Switch" statement to route Google Forms based on their MIME type.
2. Copy the file name from the Google Form.
3. Hardcode the category as "Google Forms."
4. Move the form to a designated "Forms" folder.

Handling Miscellaneous Files with a "Fallback"

A "Fallback" route is added to handle files that cannot be categorized. These files are moved to a "Miscellaneous" folder for manual review.

Processing Past Files with a Scheduled Trigger

The video explains how to process past files using a scheduled trigger.

1. Use a scheduled trigger to search for files in Google Drive.
2. The rest of the workflow is similar to the file creation workflow.

Processing Images, Audio, and Video Files

The workflow for images, audio, and video files is similar to the document processing workflow.

1. Use a scheduled trigger to search for files in Google Drive.
2. Use a "Switch" statement to route files based on their MIME type.
3. Download the file.
4. Use an "If" statement to check if the file was successfully downloaded.
5. Use OpenAI to analyze the image (using the "Analyze Image" module), transcribe the audio (using the "Transcribe Audio" module), or analyze the video (using Gemini).
6. Use OpenAI to categorize the file.
7. Move the file to the correct folder, rename it, and add it to the Google Sheet.

Processing Email Attachments

The video briefly mentions processing email attachments.

1. Use a Google Gmail trigger to watch for new emails.
2. Use a code block (written by ChatGPT) to extract PDF attachments.
3. Upload the PDF attachments to the Google Drive root folder.
4. The file creation workflow will then automatically categorize and move the files.

Conclusion

The video provides a detailed guide on how to automate Google Drive organization using an NN workflow. The workflow uses MIME types, OpenAI, and Google Sheets to intelligently categorize and track files. The presenter emphasizes that the core logic of the workflow is the same for different file types, requiring only minor adjustments. The blueprints for the workflow are available for free download.