How Do Custom AI Chatbots Actually Work?

To understand our approach, it’s important to first grasp how large language models (LLMs) like ChatGPT and Gemini work. These models are trained on a massive, but fixed, dataset. Their knowledge is essentially frozen in time at the point their training was completed. You can’t truly “retrain” or “extend” this core model with your own private business documents.

So, how can a chatbot answer questions about information it was never trained on?

This is where Retrieval-Augmented Generation (RAG) comes in. It’s a clever process that gives the LLM the context it needs, right when it needs it. Here’s how it works in a nutshell:

1. A question is asked: A user asks your chatbot something specific about your business.
2. A search on your documents is performed: Instead of immediately asking the LLM, the system first searches through your private documents (your knowledge base) to find text snippets that are relevant to the user’s question.
3. Context is provided: This relevant information is then bundled together with the original question and sent to the LLM. This makes the LLM capable to provide customized answers, related to your business, to the user’s question.
4. A response is returned: The LLM generates a direct answer to the user’s query based on the context it just received and sends it back.

This is exactly what we are going to build, but we’ll be simplifying the “search” part significantly by using Google Apps Script and Google Drive.

Our Solution: A Powerful Free Chatbot Built Entirely on Google Drive

Now that we understand the theory of RAG, let’s talk about how we’ll build our own version using a much simpler toolkit. Instead of a complex cloud environment, our entire chatbot will live within your Google account.

Here’s the plan:

1. Knowledge Base:
   We’ll use a Google Drive folder to store all the business-related documents you want the chatbot to know about. This is our version of the search database.
2. Application:
   We will create a single Google Apps Script Web App. This script will handle everything—it will be the user-facing chat window (frontend) and the logic that communicates with the AI (backend).

The magic behind this simplified approach lies in five key tricks:

1. Providing Full Context, Every Time:
   Instead of a sophisticated search, we’re taking a more direct route. We will read the entire content of every document in your Google Drive folder. This complete text, along with your system prompt, is attached to every single message you send to the AI. This ensures the model always has the full context to answer questions based on your documents.
2. Smart Caching for Speed:
   Reading every document on every turn would be slow and inefficient. To solve this, we’ll use the built-in Apps Script Caching Service. When the app first runs, it will export the content from all your documents and store it in a temporary cache. This cache lasts for up to six hours, meaning the content can be instantly retrieved and attached to your prompts without re-reading the files each time, making the conversation fast and smooth.
3. Leveraging a Massive Context Window:
   This entire method is made possible by using Google’s Gemini 2 Flash model. This powerful AI is currently free to use via the AI Studio API and, most importantly, features a massive 1 million token context window. This huge capacity allows us to feed it a large amount of text (the entire content of your documents) in a single API call, something that wasn’t practical with older models.
4. Preserving Document Structure with Markdown:
   Our business information will be stored as Google Documents. When we provide this information to the AI, we won’t just send plain text. We will use a built-in Google Drive feature to export the documents in Markdown format. Because LLMs understand Markdown, we can preserve crucial formatting like headlines, lists, and even tables. This helps the AI better understand the structure and hierarchy of your information, further improving the quality and accuracy of its answers.
5. Implementing a Simple Human Handoff:
   Our solution won’t have a live queue of human operators. But what happens if the chatbot gets stuck or a user specifically asks for a person? We will handle this through smart prompting. We will instruct the agent that if it cannot answer a question, its job is to ask the user for their contact information. Once provided, the script will automatically forward the entire conversation history to a specific Google Workspace Chat, ensuring no query is lost and a real person can follow up.

Try It Out For Yourself

Theory is great, but there’s no better way to understand the final product than to interact with it directly. I have built a demonstration version of this chatbot for you to test.

Note: For security reasons, the preview/demo is only available to visitors logged into a Google account.

Click Here to Try the Live Demo Bot

For the sake of this tutorial, I have trained this bot on publicly available information about the Santander UK Personal Loan product. You can ask it questions about interest rates, loan terms, and eligibility criteria based on that data. It can also perform a basic monthly repayment calculation if you provide a loan amount and a term.

Important Disclaimer: Please be aware that this is a demonstration model only. The training data is not continuously updated and may be out of date. Furthermore, the loan calculation is a simplified estimate, as the exact formula used by Santander is not public knowledge. The results and calculations provided by this demo bot will not match the official figures from the bank.

The Soul of the Chatbot: The System Prompt

The real magic that brings our chatbot to life isn’t just the code, but the detailed instructions we give to the AI model. This is called the “system prompt,” and it acts as the chatbot’s core personality, rulebook, and job description all in one. Every single conversation starts with this hidden set of commands.

In essence, we command the AI to become a helpful customer support expert for a specific brand. Its knowledge is strictly limited to the content we dynamically inject from your Google Drive documents, and it’s forbidden from revealing this source, making it sound like a true authority. The prompt gives it a clear list of tasks: stay on topic, answer questions from the provided context, and perform loan calculations using a specific mathematical formula always clarifying that results are estimates.

To handle situations where it gets stuck or the user asks for a person, it follows a simple human handoff protocol by asking for contact information.

Step-by-Step Guide: Building Your Chatbot in 5 mins

Ready to build? Follow these steps precisely, and you’ll have your custom AI chatbot running in no time. We’ll set up the document storage, create the app, and deploy it for the world to see.

Step 1: Create Your Knowledge Base in Google Drive

This is where your chatbot will learn.

1. Go to your Google Drive.
2. Create a new, dedicated folder. Let’s call it My Chatbot Knowledgebase.
3. Get the Folder ID: Click on the folder to open it. Look at the URL in your browser’s address bar. The last part of the URL is the Folder ID. Copy this ID—you’ll need it soon. For example, if the URL is https://drive.google.com/drive/folders/1a2b3c4d5e6f7g8h9i0j, your Folder ID is 1a2b3c4d5e6f7g8h9i0j.
4. Inside this folder, create one or more Google Docs files. Fill these documents with the information you want your chatbot to know about your product or business. Use headings, lists, and tables as you normally would. This is the content that will be fed to the AI.

Step 2: Create the Google Apps Script Project

This will be the engine of our chatbot.

1. Go back to your Google Drive.
2. Click New > More > Google Apps Script. This will open a new script project in a new tab.
3. Give your project a name at the top left, for example, “My AI Chatbot”.

Step 3: Add the Code Files

Your project needs two files: one for the backend logic (Code.gs) and one for the chat interface (index.html).

1. You will already have a file named Code.gs. This is where our backend code will go.
2. Create the frontend file: Click the + icon next to “Files” in the sidebar and choose HTML. Name the new file exactly index (it will be saved as index.html).
3. Copy and Paste the Code: Now, you will simply copy the complete code for Code.gs and index.html from the sections below and paste it into the corresponding empty files in your Apps Script project.

Content of the Code.gs file

The content of the index.html file

Step 4: Configure Your Script

You need to tell your script where to find your documents and how to access the AI.

1. Open your Code.gs file.
2. Find the lines at the top that say DRIVE_FOLDER_ID and GEMINI_API_KEY.
3. Replace DRIVE_FOLDER_ID with the Google Drive Folder ID you copied in Step 1.
4. Get your Gemini API Key:
   1. Go to Google AI Studio at https://aistudio.google.com/
   2. Click on “Get API key” and then “Create API key in new project”.
   3. Copy the generated key.
5. Go back to Code.gs and replace GEMINI_API_KEY with the key you just copied.
6. Set the Google Chat Webhook URL (for Human Handoff)
   Important Note: This feature is only available for Google Workspace accounts (e.g., yourname@yourcompany.com). It is not available for personal Gmail accounts. You must also perform these steps on a computer, not a mobile device.
   1. Open Google Chat in your browser.
   2. Go to the Space where you want to receive handoff notifications.
   3. At the top, click the down-arrow arrow next to the space’s name, then select Apps & integrations.
   4. In the dialog box that appears, click + Add webhooks.
   5. Give your webhook a name (e.g., “Chatbot Handoffs”) and click Save.
   6. A new webhook will appear in the list. Click the copy icon to copy the full webhook URL to your clipboard.
   7. Go back to your Code.gs file and replace 'YOUR_GOOGLE_CHAT_WEBHOOK_URL_HERE' with the URL you just copied.

Step 5: Finetune Your System Prompt

The provided prompt is a great starting point, but the real power comes from customizing it to your exact needs.

1. In your Apps Script project, go to the Code.gs file and find the systemInstructionText variable.
2. Modify it! Change the brand name, adjust the personality or add new rules specific to your business. This is how you make the chatbot truly yours.

Step 6: Deploy the Web App & Grant Permissions

This is the final step to make your chatbot live.

1. Click the blue Deploy button in the top-right corner and select New deployment.
2. A dialog box will appear. Click the gear icon next to “Select type” and choose Web app.
3. Fill in the settings:
   1. Description: Give it a name, like “My First AI Chatbot”.
   2. Execute as: Me (you@gmail.com)
   3. Who has access: Anyone (This makes your chatbot public. Choose “Anyone with a Google Account” if you prefer).
4. Click Deploy.
5. Authorize Permissions: Google will now ask you to grant permission for the script to run. This is a critical step.
   1. Click Authorize access.
   2. Choose your Google account.
   3. You will see a warning screen saying “Google hasn’t verified this app.” This is normal for personal scripts. Click Advanced, and then click Go to [Your Project Name] (unsafe).
   4. Finally, review the permissions (it will need to access Google Drive, connect to external services, etc.) and click Allow.

After you deploy, a window will show you the Web app URL. This is the link to your live chatbot! Copy that URL, paste it into a new browser tab, and start chatting with your custom-trained AI assistant.

Conclusion: A Powerful Tool with Clear Trade-offs

We’ve successfully built a custom-trained AI chatbot using nothing but Google Drive and Apps Script. This approach offers a fantastic, accessible entry point into the world of custom AI. Let’s summarize its advantages and limitations compared to a traditional, more complex RAG solution.

Advantages of Our Simple Solution

• Incredible Simplicity: You don’t need to understand vector databases, cloud architecture, or complex data pipelines. If you can use Google Drive, you can build this.
• Extremely Cost-Effective: For small-to-medium use cases, this solution is virtually free. You’re leveraging your existing Google account and the generous free tier of the Gemini API.
• Speed of Development: You can go from idea to a working prototype in an afternoon, not weeks.
• Perfect for SMBs and Internal Tools: This is an ideal solution for small businesses wanting to add a support bot, or for creating an internal tool to help your team query company knowledge bases.

Limitations Compared to a Proper RAG

• Scalability: Our “read everything” approach works brilliantly for a dozen or even a few dozen documents. It does not scale to thousands of documents. A proper RAG system is designed to handle massive knowledge bases efficiently.
• No “Smart” Retrieval: A true RAG system uses semantic search to find the most relevant snippets of text to answer a question. Our solution sends the entire knowledge base every time. While this works well with a large context window (roughly 750,000 words at once.), it can sometimes be less precise than targeted retrieval.

Basic Handoff: The email-based handoff is a simple notification system. It lacks the features of a professional customer support platform with live agent queuing and real-time chat.

How to Add It to Your Website

We’ve now created a great chatbot. While it doesn’t have robust queuing or a real-time human handoff capability, and it isn’t designed for a massive volume of training text, it stands out as a reliable and incredibly cheap solution. For many small and medium-sized businesses, this can be a fantastic way to improve the quality of service for their users without a major investment.

The natural next step is to move this chatbot from a standalone web app to a live, customer-facing tool on your website. In my next tutorial, we will review exactly how to implement it into a real-life site, as that requires a few modifications to the code we just created. Stay tuned!

This free, cloud-based scripting platform, seamlessly integrated within Google Workspace, holds huge potential to streamline your workflows, connect your favorite Google applications, and build custom solutions without the need for extensive development experience.

In this post, we’ll pull back the curtain on Google Apps Script, exploring exactly what it is, its core purpose and impressive benefits, and how it is compared against popular no-code alternatives.

What is Google Apps Script?

It’s a tool from Google that lets you write code to make your Google apps do more work for you automatically. It’s like a special language for telling Google Sheets, Docs, and other apps what to do.

This tool runs online, in the cloud, and its main goal is to help you automate boring tasks and get more done without extra effort.

Google Apps Script has been around for a while – since 2009, actually. So, it’s a tried-and-true service that many people have been using for years to improve their work.

One of the greatest things about it is how well it works with the Google tools you already know. It connects directly with apps like Google Sheets, Google Docs, Google Forms, Gmail, Calendar, and more. This connection lets you build custom links between them. For example, you can have new form answers automatically update a spreadsheet or send a personalized email. You can even connect Google apps to other online services that are not from Google using something called APIs.

With Google Apps Script, you’re not just limited to simple tasks. You can build more advanced things like web pages that run your code (called Web apps), create small pieces of code other programs can use (like Executable APIs for third-party tools), or even build add-ons that others can install in their Google Workspace apps.

This makes Google Apps Script super flexible and useful for creating custom solutions that solve your unique problems or automate specific steps in your business workflow.

It’s also worth mentioning AppSheet here. AppSheet is another tool from Google, but it’s a no-code platform for building apps without writing much (or any) code. When you use Google Apps Script and AppSheet together, you can create even more powerful and complete custom apps to really streamline and automate your business processes.

This power and flexibility, available for free within Google Workspace, is why Google Apps Script is such a valuable tool, even if it doesn’t always get the spotlight.

What does Apps Script code look like?

The following simple code example demonstrates writing “Hello, Apps Script!” into the top-left cell of a spreadsheet.

function sayHello() {
 // Get the active spreadsheet that the script is attached to
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

 // Get the first sheet in the spreadsheet (Sheets are indexed starting from 0)
 var sheet = spreadsheet.getSheets()[0];

 // Get the cell range where we want to put the text (A1 in this case)
 var cell = sheet.getRange('A1');

 // Set the value of the cell to "Hello, Apps Script!"
 cell.setValue('Hello, Apps Script!');
}

Benefits of Using Google Apps Script

Let’s look at why it’s such a valuable tool for your work or business.

Here are some of the great things about using Google Apps Script:

• Saves Time and Effort: It automates those repetitive tasks you do often, like moving data or sending similar emails. This frees you up to focus on more important work.
• It’s Free: If you have a Google Account, you already have access to Google Apps Script at no extra cost.
• Reliable: It’s a Google service, so it’s generally very dependable and runs consistently, just like other Google apps.
• Seamless Integration with Google Workspace: If your business uses Google Workspace, Apps Script fits perfectly into your existing setup (Sheets, Docs, Gmail, etc.). You don’t need external platforms to connect your Google apps. It also works well with Google’s ways of managing access.
• Connects Beyond Google Workspace: Apps Script isn’t just for Google apps! You can use it to connect your Google Workspace environment with many other popular third-party services using their APIs. This lets you pull data into Sheets from a project management tool like ClickUp or Asana, add new contacts from a Google Form to Mailchimp or CRM systems like HubSpot, send notifications to Slack or Twilio, or work with data from platforms like Stripe or Shopify. It effectively brings external tools into your familiar Google workflow.
• Cheap to Develop: It uses JavaScript, a common language. This means finding someone to help build scripts is often easier and less expensive than hiring for other complex systems.
• Improves Accuracy: Scripts follow instructions exactly, reducing human errors that can happen during manual, repetitive work.
• Increases Productivity: By automating small tasks, you and your team can spend more time on meaningful work, making everyone more productive.
• Cost-Effective Automation: You get powerful automation features without the high cost of many specialized automation tools or hiring full development teams.

Schedule Your Scripts to Trigger Automations

One of the most powerful features of Google Apps Script is the ability to run your scripts automatically without you needing to click a button. These automations are set up using “triggers.” Think of triggers as the events or schedules that tell your script when to wake up and perform a task. This allows you to create workflows that happen completely on their own, saving you manual effort.

Google Apps Script triggers can be thought of in two main ways:

• Document or Content Related Triggers (Simple Triggers): These are easy, built-in triggers tied directly to basic interactions with Google Workspace files like Docs, Sheets, and Forms. They automatically run when someone opens or edits the file, or even changes their selection in a Sheet. These are great for simple tasks directly related to the content or a user’s basic actions within a document.
  • Opening a file: Runs when a document, sheet, or form is opened for editing.
  • Editing content: Runs when a cell’s value is changed in a spreadsheet.
  • Changing selection: Runs when the selected cell(s) change in a spreadsheet.
  • Installing an Add-on: Runs when someone installs your script as an add-on.
• Process Related Triggers (Installable Triggers): These are more powerful and flexible triggers that you set up yourself. They are not limited to simple document interactions and can initiate scripts based on a wider range of events or on a schedule. They can also perform tasks that require your authorization, like sending emails or interacting with external services.
  • Based on a schedule:
    • Running at a specific time and date.
    • Running repeatedly at set intervals (like every hour, day, or even every few minutes).
  • Based on events:
    • Opening a file (a more powerful version than the simple trigger).
    • Editing content (a more powerful version than the simple trigger).
    • Changes to the document structure (like adding or deleting rows/columns in a Sheet).
    • Submitting a form.
    • Updates to your Google Calendar events (created, updated, or deleted).
• Triggered by external services (Webhooks): Your script can be set up with a unique web address that external services can call to trigger the script’s execution. This is like setting up a direct line for other online tools to tell your script to run, enabling integrations and automated workflows between different services.

By using Process Related Triggers, you can automate more complex workflows, connect different Google Workspace applications, and even integrate with services outside of Google, all running automatically based on your defined triggers or external calls.

Google Apps Script vs. Popular No-Code Automation Platforms: A Different Perspective

When exploring automation options, you’ve likely come across services branded as “no-code.” Platforms like Zapier, Make (formerly Integromat), N8N or Microsoft Power Automate are popular examples, promising to let anyone build complex automation workflows using visual interfaces, drag-and-drop elements, and pre-built connectors without writing any code. While these tools are powerful and have their place, the “no-code” label can often be misleading and create a false impression of universal accessibility.

Here’s the reality: these “no-code” platforms are, at their core, visual programming interfaces. They abstract the code away, but they still require users to understand fundamental programming and data concepts to be used effectively. If you don’t know what an API is and how services communicate via requests (like GET or POST), what a payload is, how to use boolean logic (true/false conditions), or how data types work, you will struggle to configure complex flows, debug issues, or understand why an integration isn’t behaving as expected.

Setting up even seemingly simple connections often involves mapping data fields, understanding data formats (like JSON), and configuring triggers and actions based on underlying API capabilities – all concepts rooted in programming.

This is where the “misleading” aspect comes in. The visual interface suggests simplicity, but the conceptual hurdle remains significant for someone with absolutely no technical background. It gives the false impression that anyone can instantly become an automation expert just by signing up.

Google Apps Script, on the other hand, presents itself clearly as a scripting or “low-code” solution. While it requires writing code (JavaScript), this transparency sets realistic expectations from the start. You know you are learning a programming language, which provides a solid foundation for understanding how automation and integrations truly work.

Furthermore, in the age of advanced AI, learning and using Google Apps Script has become significantly more accessible than navigating the complexities often hidden beneath the visual layer of no-code tools. While AI can help with configuring some aspects of no-code platforms, tools like Gemini excel at providing high-quality, detailed script examples, explaining code line by line, and helping users understand the underlying logic in JavaScript. This level of detailed, context-aware assistance for writing and debugging code is not typically available or as effective when trying to understand the intricate configurations and hidden data structures within complex no-code workflows.

We believe that, with the aid of powerful AI, starting with Google Apps Script and learning foundational coding concepts can actually be a more recommended and ultimately easier path to building robust, custom automations than wrestling with the technical prerequisites disguised by a “no-code” interface.

Beyond the learning curve and AI assistance, Google Apps Script offers several distinct advantages over many popular no-code systems:

• Deeper Google Workspace Integration: Apps Script is built directly into Google Workspace, allowing for unparalleled interaction with Google Sheets, Docs, Gmail, Calendar, and other services at a granular level.
• Freeness: If you have a free Google account, Google Apps Script is entirely free to use, unlike many no-code platforms that require paid subscriptions for significant usage or advanced features.
• Flexibility and Customization: While no-code tools rely on pre-built connectors and action limitations, Apps Script allows you to write custom logic for almost any scenario and interact with APIs exactly as needed, offering far greater flexibility.
• Powerful In-Document Triggers: As discussed previously, Apps Script provides unique trigger options tied directly to user interactions within Google documents (like edits or selections), which are often not available or are less seamlessly integrated in external no-code platforms.

In summary, while no-code platforms serve a valuable purpose for users with some technical intuition or specific, straightforward needs, they can be a frustrating starting point for complete beginners due to the hidden technical concepts. Google Apps Script is a powerful tool that shouldn’t be dismissed in favor of solutions that only appear simpler on the surface.

Getting Started with Google Apps Script

Ready to dip your toes into the world of Google Apps Script? It’s easy to start experimenting, and Google provides two primary ways to create and run your scripts: Bounded Scripts and Standalone Scripts.

Bounded Scripts

Bounded scripts are tied directly to a specific Google file – a Sheet, Doc, Form, or Slide. They are perfect for automating tasks that are relevant only to that particular file, like adding custom menus, responding to edits in a spreadsheet, or processing form submissions. Key feature of bounded scripts is their ability to use simple triggers, like onOpen, onEdit, onInstall, and onFormSubmit. These functions automatically run when a specific event occurs in the linked file, without you needing to manually trigger the script.

You can easily access the script editor for a bounded script directly from the file you want to work with. For instance, in Google Sheets, simply go to the menu and click Extensions > Apps Script. This will open a new browser tab with the script editor, already linked to your spreadsheet.

/**
 * This Google Apps Script is designed to be bounded to a Google Sheet.
 * It monitors changes in Column B. When a cell in Column B is set to "Confirmed",
 * cell in Column A, and then sends this document as a PDF attachment to a specified email address.
 * The email subject will also be the title from Column A.
 */

// --- Configuration Variables ---
/**
 * The email address to which the generated Google Document will be sent.
 */
const TARGET_EMAIL = 'your-target-email@gmail.com'; // <<< IMPORTANT: Update this email address!

/**
 * The folder ID where the new Google Documents will be created.
 */
const TARGET_FOLDER_ID = 'YOUR-TARGET-FOLDER-ID'; // Optional: Set a specific Google Drive Folder ID. Leave empty for root.

/**
 * The value in the status column that triggers document creation and email sending.
 * @type {string}
 */
const CONFIRM_STATUS_VALUE = 'Confirmed';

// --- Main Function Triggered on Sheet Edit ---

/**
 * This function is automatically triggered when a user edits a cell in the bounded spreadsheet.
 * It checks if the edited cell is in Column B and if its value is 'Confirmed'.
 * If conditions are met, it creates a Google Doc and sends it as an email attachment.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e The event object containing information about the edit.
 */
function onEditTrigger(e) {

  try {
    const range = e.range;
    const sheet = range.getSheet();
    const editedColumn = range.getColumn();
    const editedRow = range.getRow();
    const newValue = e.value; // The new value of the edited cell

    // Column A (index 1) for title
    const TITLE_COLUMN_INDEX = 1;
    // Column B (index 2) for status dropdown
    const STATUS_COLUMN_INDEX = 2;

    // Check if the edited column is the status column (Column B) and the new value is 'Confirmed'
    if (editedColumn === STATUS_COLUMN_INDEX && newValue === CONFIRM_STATUS_VALUE) {

      // Get the title from the corresponding row in the title column (Column A)
      const documentTitleRange = sheet.getRange(editedRow, TITLE_COLUMN_INDEX);
      const documentTitle = documentTitleRange.getValue();

      // Create the Google Document
      const doc = createGoogleDocument(documentTitle);
      const docId = doc.getId();

      // Get the Google Doc file directly for attachment
      const googleDocFile = DriveApp.getFileById(docId);

      // Send the email with the Google Doc file as an attachment
      sendDocumentEmail(documentTitle, googleDocFile);

      // Optional: Add a note to the sheet indicating the document was processed
      const statusNoteCell = sheet.getRange(editedRow, editedColumn + 1);
      statusNoteCell.setValue('Document sent!');

    }
  } catch (error) {
    Logger.log(`An error occurred in onEdit: ${error.message}. Stack: ${error.stack}`);
  }
}

// --- Helper Functions ---

/**
 * Creates a new Google Document with the specified title and optional content.
 *
 * @param {string} title The title for the new Google Document.
 * @returns {GoogleAppsScript.Drive.File} The newly created Google Document file.
 */
function createGoogleDocument(title) {
  // Create a new document with the given title
  const newDoc = DocumentApp.create(title);
  const body = newDoc.getBody();

  // Add some default content to the document (optional)
  body.appendParagraph(`This document was automatically generated from your Google Sheet.`);
  body.appendParagraph(`Title: ${title}`);

  // Save and close the document to ensure content is written
  newDoc.saveAndClose();

  // Get the file object from DriveApp
  const file = DriveApp.getFileById(newDoc.getId());

  // Move the file to the target folder if TARGET_FOLDER_ID is specified
  if (TARGET_FOLDER_ID) {
    try {
      const folder = DriveApp.getFolderById(TARGET_FOLDER_ID);
      file.moveTo(folder);
      console.log(`Moved document "${title}" to folder ID: ${TARGET_FOLDER_ID}`);
    } catch (folderError) {
      console.warn(`Could not move document to folder ID ${TARGET_FOLDER_ID}: ${folderError.message}. Document remains in root.`);
    }
  }

  return file;
}

/**
 * Sends an email with the specified subject and attaches the provided PDF blob.
 *
 * @param {string} subject The subject of the email.
 * @param {GoogleAppsScript.Base.Blob} attachmentBlob The PDF blob to attach to the email.
 */
function sendDocumentEmail(subject, attachmentBlob) {
  const emailBody = `Dear recipient,

A new document titled "${subject}" has been generated and is attached.

Best regards,
Your Google Sheet Automation`;

  MailApp.sendEmail({
    to: TARGET_EMAIL,
    subject: subject,
    body: emailBody,
    attachments: [attachmentBlob]
  });

}

Standalone Scripts

Standalone Scripts are not attached to any specific Google document. They live in your Google Drive and are ideal for tasks that aren’t tied to a single file, such as automating processes across multiple documents, creating web apps, or setting up time-driven tasks (like sending out scheduled reports) and interacting with other services like Gmail or Calendar.

You can create a Standalone Script by going to Google Drive, clicking + New > More > Google Apps Script. This also opens the script editor in a new tab, but this project isn’t linked to any document by default.

/**
* Creates a Google Doc and sends an email to the current user with a link to the doc.
 */
const FOLDER_ID = 'YOUR_FOLDER_ID'; // REplace it with your Drive folder ID
function createAndSendDocument() {
  try {
    // Create a new Google Doc named 'Hello, world!'
    const doc = DocumentApp.create('Hello, world!');
    // Access the body of the document, then add a paragraph.
    doc.getBody().appendParagraph('This document was created by Google Apps Script.');
    // Get the URL of the document.
    const url = doc.getUrl();
    // Get the ID of the newly created document.
    const docId = doc.getId();
    // Get the file object of the document from its ID.
    const file = DriveApp.getFileById(docId);
    // Get the target folder by its ID.
    const folder = DriveApp.getFolderById(FOLDER_ID);
    // Add the file to the specified folder.
    // This also implicitly removes it from the user's My Drive root folder if it was created there.
    file.moveTo(folder);
    // Get the email address of the active user - that's you.
    const email = Session.getActiveUser().getEmail();
    // Get the name of the document to use as an email subject line.
    const subject = doc.getName();
    // Append a new string to the "url" variable to use as an email body.
    const body = 'Link to your doc: ' + url;
    // Send yourself an email with a link to the document.
    GmailApp.sendEmail(email, subject, body);
  } catch (err) {
    Logger.log('Failed with error %s', err.message);
  }
}

These simple examples should take you less than two minutes to set up and run, giving you a feel for how easy it is to start automating with Google Apps Script!

To truly unlock the full power and explore the vast capabilities of Google Apps Script, your next essential stop should be the official Google Apps Script documentation. This is your primary, authoritative source for learning everything from the basics to advanced topics.

The official site provides comprehensive guides on core concepts, detailed reference materials for all the built-in Google services (like SpreadsheetApp, DocumentApp, GmailApp, and many more), tutorials for common tasks, and information on best practices.

Conclusion: Unlock the Power of Google Apps Script

Google Apps Script is a powerful and free tool, often overlooked, that unlocks significant automation potential within the Google Workspace ecosystem and beyond. By understanding its capabilities and the clear distinction it offers compared to the often misleading simplicity of “no-code” solutions, you can start building custom workflows tailored to your specific needs. From streamlining repetitive tasks to creating sophisticated integrations with external services, Apps Script offers a flexible and cost-effective way to boost productivity.

Ready to dive deeper and see Google Apps Script in action? Be sure to check out our other blog posts where we provide practical automation tutorials and explore more advanced use cases to help you continue your automation journey!

What is Web Scraping?

Web scraping is akin to automatically copying and pasting information from websites. Imagine having a digital assistant that visits websites and automatically collects specified information. Instead of manually selecting and copying text or images, a program performs this task for you, gathering data such as prices, product details, or contact information.

This extracted data can then be used for various purposes, including comparing prices across different online retailers, collecting sales leads, creating notifications for content changes, and more. Essentially, web scraping automates tedious manual work, saving significant time and effort by efficiently extracting and organizing large amounts of web data.

Why Use Web Scraping for Businesses?

In today’s competitive business environment, productivity and optimized resource allocation are crucial for success. Web scraping is a valuable tool for achieving these goals, significantly improving productivity by automating time-consuming data collection and analysis.

By automating the extraction of data from websites, businesses save considerable time and effort compared to manual methods. This increased efficiency allows teams to focus on strategic initiatives rather than repetitive tasks.

Furthermore, the data gathered through web scraping is not merely an end in itself; it serves as crucial input for further automation processes. For instance, scraped data can power dynamic, competition-based pricing strategies in e-commerce or automatically identify valuable investment opportunities by aggregating real estate listings from multiple portals.

This seamless integration with other automation workflows amplifies the impact of web scraping, making it an indispensable asset for any forward-thinking business.

Introducing Google Apps Script and Google Sheets

Google Apps Script is a cloud-based scripting language built on JavaScript that empowers you to automate tasks and build powerful integrations within Google Workspace (formerly G Suite). Its unique advantage lies in its ability to seamlessly connect various Google services, including Gmail, Docs, Drive, Calendar, and, importantly, Sheets, making it exceptionally well-suited for building comprehensive automations. This interconnectedness allows you to create workflows that span across multiple applications, streamlining processes and boosting productivity.

For web scraping specifically, Apps Script’s UrlFetchApp service allows you to fetch content directly from web pages, enabling automated data extraction and integration with the rest of your Google Workspace applications. By sending HTTP requests to websites, you can retrieve HTML, XML, or JSON data. This extracted data can then be parsed and structured using JavaScript within the script.

The true power of this approach is realized when combined with Google Sheets: scraped data can be directly written into spreadsheets, providing a convenient and familiar environment for data storage, organization, analysis, visualization, and further automation.

Step-by-Step Guide to Web Scraping with Google Apps Script and Google Sheets

The Idea

We’ll create a system that does the following:

1. Reads a list of website addresses (URLs) from a Google Sheet.
2. Visits each website and grabs its HTML content.
3. Finds the predefined information (product price in our case) within that HTML.
4. Writes the extracted prices back to the Google Sheet.

Setting up the Google Sheet

First, you need a Google Sheet.Your Google Sheet should have at least two columns:

• Column A (Column 1): This column will contain the website addresses (URLs) of the products you want to track.
• Column B (Column 2): This column will contain the CSS selector used to identify and extract the specific element (like the price) from the web page.
• Column C (Column 3): This column will store the prices extracted from the websites.

Here you can find a template for the spreadsheet.

Finding the Right Element: How to Get the CSS Selector

The next crucial step for your data extraction tool is telling it exactly which piece of information to grab from the webpage. This is where the CSS selector comes in.

Think of a CSS selector as an address for a specific element on a web page – like the price, a product title, or a review count. Here’s how you can easily find this address using the Chrome browser:

1. Open the Webpage: Go to the specific product page (or any page with the element you want to extract).
2. Open Developer Tools: Right-click anywhere on the page and select “Inspect”. This opens the Developer Tools panel, usually at the bottom or side of your browser window. Make sure you’re on the “Elements” tab.
3. Use the Element Picker: In the top-left corner of the Developer Tools panel, you’ll see a small icon that looks like an arrow inside a box (or sometimes just a cursor icon). Click on this icon. This activates the “select element” mode.
4. Hover and Click: Now, move your cursor over the elements on the webpage. As you hover, the element under your cursor will be highlighted. Click on the specific element you want to extract (e.g., the price display).
5. Locate in the HTML: Clicking the element picker will jump to and highlight that element’s code within the “Elements” tab of the Developer Tools.
6. Copy the Selector: Right-click on the highlighted line of code in the Developer Tools panel. In the context menu that appears, hover over Copy and then click Copy selector.

That’s it! The CSS selector for that specific element is now copied to your clipboard. This is the value you would then paste into Column C of your Google Sheet for that product URL, telling your extraction script exactly what to look for on that page.

The Code (Google Apps Script)

Now, let’s look at the code that does the magic. Open the script editor in your Google Sheet by going to “Tools” > “Script editor”. Copy and paste the code provided below into the script editor.

/**
 * Fetches product prices from URLs in a Google Sheet and updates the sheet with the prices.
 */

// Configuration (Update these with your actual values)
const SHEET_ID = `YOUR_SHEET_ID`; // Replace with your actual Sheet ID
const SHEET_NAME = `YOUR_SHEET_NAME`; // Replace with your actual Sheet Name
const URLS_COLUMN_ID = 1; // Column containing URLs (A = 1)
const CSS_SELECTOR_COLUMN = 2; // Column containing CSS selectors (B = 2)
const PRICE_COLUMN = 3; // Column to write prices (C = 3)
const REQUEST_DELAY = 1000; // Delay between requests in milliseconds (1 second)


/**
 * Main function to run the script.
 */
function main() {
  const urlData = getUrlsFromSheet();
  if (urlData.length === 0) {
    Logger.log("No URLs to process.");
    return;
  }

  const prices = [];
  for (let i = 0; i < urlData.length; i++) {
    const url = urlData[i].url;
    const selector = urlData[i].selector;
    const html = fetchHtml(url);
    const price = extractPrice(html, selector);
    prices.push(price);
    Utilities.sleep(REQUEST_DELAY); // Delay between requests
    Logger.log(`Processed URL ${i+1}/${urlData.length}: ${url}, Price: ${price}`);
  }

  updateSheet(prices);
}


/**
 * Opens the spreadsheet and gets the URLs and selectors.
 * @return {Array<{url: string, selector: string}>} An array of objects containing URLs and selectors.
 */
function getUrlsFromSheet() {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return []; // Handle empty sheet

  const urls = sheet.getRange(2, URLS_COLUMN_ID, lastRow - 1)
    .getValues()
    .flat(); // Get values as a 1D array.

  const selectors = sheet.getRange(2, CSS_SELECTOR_COLUMN, lastRow - 1)
    .getValues()
    .flat(); // Get values as a 1D array.

  const urlData = [];
  for (let i = 0; i < urls.length; i++) {
    urlData.push({
      url: urls[i],
      selector: selectors[i]
    });
  }
  return urlData;
}


/**
 * Fetches the HTML content of a URL.
 * @param {string} url The URL to fetch.
 * @return {string|null} The HTML content, or null if there's an error.
 */
function fetchHtml(url) {
  try {
    const options = {
      method: 'get',
      muteHttpExceptions: true,
      headers: { // Added some common headers
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36',
        'Accept-Language': 'en-US,en;q=0.9',
        'Cache-Control': 'no-cache'
      }
    };
    const response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() === 200) {
      return response.getContentText();
    } else {
      Logger.log(`Error fetching ${url}: ${response.getResponseCode()} - ${response.getContentText()}`);
      return null;
    }
  } catch (error) {
    Logger.log(`Error fetching ${url}: ${error}`);
    return null;
  }
}


/**
 * Extracts the price from the HTML using Cheerio.
 * @param {string} html The HTML content.
 * @param {string} selector The CSS selector to use.
 * @return {number|null} The extracted price as a number, or null if not found.
 */
function extractPrice(html, selector) { // Added selector parameter
  if (!html || !selector) return null; // Added check for selector

  try {
    const $ = Cheerio.load(html);
    let priceText = $(selector).text(); // Use provided selector
    if (!priceText) {
      Logger.log("Price element not found using selector: " + selector);
      return null;
    }
    priceText = priceText.replace(/,/g,'.');
    let price = parseFloat(priceText.replace(/[^0-9.]/g, ''));
    if (isNaN(price)) {
      Logger.log("Could not parse price to a number: " + priceText);
      return null;
    }
    return price;
  } catch (error) {
    Logger.log("Error extracting price: " + error);
    return null;
  }
}


/**
 * Updates the Google Sheet with the extracted prices.
 * @param {Array<number|null>} prices An array of prices to write to the sheet.
 */
function updateSheet(prices) {
  if (prices.length === 0) return;

  const ss = SpreadsheetApp.openById(SHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();
  const urlsCount = sheet.getRange(2, URLS_COLUMN_ID, lastRow - 1).getValues().flat().length;

  if (prices.length !== urlsCount) {
    Logger.log(`Number of prices (${prices.length}) does not match the number of URLs (${urlsCount}).`);
    return;
  }
  
  sheet.getRange(2, PRICE_COLUMN, prices.length, 1).setValues(prices.map(price => [price]));
}

Before you start using this code, please don’t forget to include the Cheerio library. This third-party library helps us process HTML more easily and must be added to our project manually. To do this:

1. In the Apps Script editor, click on the “Resources” menu and select “Libraries…”
2. A dialog box will appear. Enter the following Script ID into the input field: 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0
3. After entering the Script ID, click the “Look up” button.
4. If the Script ID is valid, the library’s information (name and versions) will appear. Choose the specific version of the library you want to use from the dropdown menu. It’s generally recommended to use a specific version rather than “latest” for stability.
5. Once you’ve selected the version, click the “Add” button.

The library should now appear in the “Libraries” dialog box.

Explanation of the Code

Our Google Apps Script for tracking product prices is built from several key functions working together. Let’s break down what each one does:

1. main()

• Purpose: This is the conductor of our orchestra! The main function is the primary entry point that orchestrates the entire process of fetching and updating prices. It calls other functions in the correct sequence to get the job done.
• Structure:
  1. It starts by calling getUrlsFromSheet() to fetch the list of URLs and their corresponding CSS selectors from your spreadsheet.
  2. It then loops through each URL/selector pair.
  3. Inside the loop, for each item:
     • It calls WorkspaceHtml() to download the webpage’s content.
     • It passes the downloaded HTML and the selector to extractPrice() to find the price.
     • It adds the found price (or null if not found) to a list.
     • It pauses briefly (Utilities.sleep()) to avoid overloading the websites.
  4. Finally, it calls updateSheet() to write all the collected prices back to the spreadsheet.
• Analogy: Think of main() as a Project Manager. It doesn’t do the specialized tasks itself, but it knows the overall goal and directs the specialists (getUrlsFromSheet, WorkspaceHtml, extractPrice, updateSheet) in the right order, ensuring the project (price tracking) is completed step-by-step.

2. getUrlsFromSheet()

• Purpose: This function acts as the data retriever. Its job is to open your specified Google Sheet and read the list of product page URLs and the CSS selectors needed to find the prices on those pages.
• Structure:
  1. Opens the Google Spreadsheet using its ID (SHEET_ID).
  2. Selects the specific sheet within that spreadsheet using its name (SHEET_NAME).
  3. Determines how many rows have data.
  4. Reads the values from the URL column (URLS_COLUMN_ID).
  5. Reads the values from the CSS Selector column (CSS_SELECTOR_COLUMN).
  6. Combines these into a list where each item contains both the URL and its corresponding selector.
  7. Returns this list of URL/selector pairs.
• Analogy: This function is like a Librarian. You give it the location (Sheet ID and Name) and the type of information you need (URLs and Selectors from specific columns), and it goes to the shelves (the Sheet), pulls out the requested lists, and hands them over neatly organized.

3. WorkspaceHtml()

• Purpose: This function’s role is to visit a specific web address (URL) and download the raw HTML source code of that page. This code is what the browser uses to display the webpage, and it contains the price information we need.
• Structure:
  1. Takes a single url as input.
  2. Uses Google Apps Script’s UrlFetchApp.fetch() service to make a request to that URL.
  3. It includes specific options (like User-Agent headers) to make the request look more like it’s coming from a standard web browser, which can help prevent being blocked by some websites.
  4. It checks if the website responded successfully (HTTP status code 200).
  5. If successful, it returns the downloaded HTML code as text.
  6. If there’s an error (like the page not found or access denied), it logs the error and returns null.
• Analogy: Imagine WorkspaceHtml() as a Web Scout. You give it an address (URL), and it goes to that location on the internet, makes a copy of the page’s underlying blueprint (the HTML), and brings that blueprint back to you. It even tries to look like a regular visitor to avoid suspicion.

4. extractPrice()

• Purpose: This is the specialized extractor. Given the raw HTML code of a page and a specific CSS selector (which acts like a map coordinate), this function finds the price text, cleans it up, and converts it into a numerical value.
• Structure:
  1. Takes the html content and the selector as input.
  2. Uses the Cheerio library (which needs to be added to your Apps Script project) to parse the HTML, making it easy to search.
  3. Uses the provided selector to pinpoint the exact HTML element containing the price.
  4. Extracts the text content from that element.
  5. Cleans the text: removes currency symbols, thousands separators (like commas), and any other non-numeric characters except the decimal point. It also standardizes decimal separators to periods.
  6. Converts the cleaned text into a floating-point number.
  7. If any step fails (e.g., selector not found, text can’t be converted to a number), it logs the issue and returns null. Otherwise, it returns the numerical price.
• Analogy: Think of extractPrice() as a Data Detective. It receives a large document (HTML) and a specific clue (the CSS selector). It uses the clue to find the exact piece of data (the price text), carefully cleans off any obscuring marks (currency symbols, commas), and reports the final numerical value found.

5. updateSheet()

• Purpose: This function is the final step, responsible for taking the list of extracted prices and writing them back into the designated column in your Google Sheet.
• Structure:
  1. Takes the prices array (collected by the main function) as input.
  2. Opens the target Google Spreadsheet and Sheet (similar to getUrlsFromSheet).
  3. Performs a safety check: It verifies that the number of prices collected matches the number of URLs originally read from the sheet to avoid writing data in the wrong rows if some fetches failed.
  4. Selects the correct range in the output column (PRICE_COLUMN), starting from the second row and spanning the necessary number of rows.
  5. Formats the simple list of prices into a 2D array format that Google Sheets requires for writing ([[price1], [price2], ...]).
  6. Writes the formatted prices into the selected range in the sheet using setValues().
• Analogy: This function is the Accountant or Scribe. It takes the final report (the list of prices) generated by the team and carefully enters each value into the correct cell in the ledger (the Google Sheet), ensuring accuracy and proper placement.

Running the Code

After pasting the code into the script editor:

1. Save the script.
2. Run the main() function. You’ll need to authorize the script to access your Google Sheet.

Important Notes:

• The extractPrice(html) function is the most website-specific part. The way prices are displayed in HTML varies from website to website. Therefore, you might need to adjust the code within this function to match the structure of the websites you’re tracking. You’ll likely need to inspect the website’s HTML source to find a suitable CSS selector.
• The code includes a 1-second delay between requests. This is important to be polite to the websites you’re scraping and avoid getting blocked.

This automated system will save you time and effort by automatically tracking product prices for you. By understanding the basics of how it works, you can even customize it further to suit your specific needs.

Scheduling the Script to Run Daily

The last step is to schedule your script to run automatically every day at the desired time.

Create a Time-Based Trigger

• Go to “Edit” -> “Current project’s triggers”.
• Click “Add Trigger”.
• Select the main() function.
• Choose “Time-driven” as the event source.
• Select “Day timer” and specify the desired time (e.g., 00:00 for midnight).
• Save the trigger.

Tips and Limitations

While Google Apps Script’s UrlFetchApp is effective for many websites, it may not be suitable for heavily protected sites that employ anti-scraping measures such as CAPTCHAs and IP blocking. Attempting to bypass these protections with Apps Script alone is often unreliable.

Scraping such portals or engaging in “black hat” practices requires dedicated third-party solutions that use techniques like rotating proxies and CAPTCHA solvers. Examples of such services include Apify, Bright Data, ParseHub, and Scraper API.

Importantly, these services can often be integrated into Google Workspace automation processes. This means you can still build a complete, automated workflow within your Workspace infrastructure, even if a third-party scraping solution is necessary. Data extracted by these services can be seamlessly fed into Google Sheets, Docs, or other Workspace apps using their APIs or webhooks. It is highly recommended to respect website terms of service and `robots.txt` and to scrape responsibly and ethically.

UrlFetchApp Quotas

Google Workspace imposes quotas and limitations on `UrlFetchApp` to prevent abuse, maintain service stability, and ensure fair usage across all users. These limitations are crucial to understand when designing web scraping scripts:

• Execution Time: There’s a daily limit on the total time your Apps Script project can run. As of this writing, this limit is typically around 6 minutes per execution for most Workspace accounts. This means if your script runs for an extended period due to numerous UrlFetchApp calls or complex processing, it might reach this limit and stop executing. However, you can often restart the script after it has stopped due to hitting the execution time limit, allowing you to process larger datasets or perform more complex tasks in stages.
• Number of Calls: There are restrictions on how many UrlFetchApp requests you can make within specific timeframes. These limits are designed to prevent overwhelming target websites with requests. As of this writing, free Google accounts typically have a daily limit of around 20,000 `UrlFetchApp` calls, while Google Workspace accounts generally have a much higher limit, around 100,000 calls per day.
• Sequential Approach vs. `fetchAll()`: Although Apps Script operates in a single-threaded environment and doesn’t offer true simultaneous fetching, using UrlFetchApp.fetchAll() allows for optimized retrieval of multiple URLs. This method significantly reduces overhead compared to sequential fetching by handling network communication more efficiently, even though the requests are not executed in parallel.

Conclusion

In summary, web scraping with Google Apps Script and Google Sheets offers a powerful, accessible, and importantly, free way for businesses to automate data collection and gain valuable insights. Google Apps Script is available for use with both free Google accounts and Google Workspace subscriptions, making it a cost-effective solution for businesses of all sizes.

Furthermore, if you’re using Google Workspace, relying on Apps Script for your web scraping needs offers unparalleled flexibility and integration within your existing business workflows. Unlike rigid third-party solutions that often require complex integrations and data transfer processes, Apps Script seamlessly connects with other Workspace apps.

We encourage you to explore the possibilities of web scraping with Google Apps Script and Sheets for your own business needs and unlock the potential of automated data collection for improved efficiency and strategic advantage. The number of scenarios where this powerful combination can be applied is virtually endless; from automating market research and competitor analysis to streamlining lead generation and content aggregation, only your imagination limits the potential use cases.