Automate Client Onboarding From Email to AppSheet with Gemini
The thrill of landing a new client is quickly replaced by the unbillable grind of manual data entry. This repetitive task is more than just tedious—it’s a hidden risk to your business.
The Boutique Consultant’s Dilemma: The Manual Data Entry Trap
You’ve done it. You’ve landed a new client. The proposal is signed, the initial invoice is paid, and the excitement is palpable. This is the moment every boutique consultant lives for—the start of a new partnership, a new problem to solve. But then comes the part that doesn’t make it into the highlight reel: the administrative grind.
You open your CRM, your project management tool, and your invoicing software. You meticulously copy the client’s name from their email signature, paste it into one field, then tab over. You find their company name, their job title, their phone number. You create a new project folder, set up a client record in AI-Powered Invoice Processor, and double-check that the email address you typed is exactly right. This is the manual data entry trap—a repetitive, unbillable, and surprisingly risky part of the client onboarding process.
Why Manual Onboarding Stifles Growth
Let’s be real: copying and pasting data isn’t the high-value strategic work your clients are paying you for. While it may seem like a minor inconvenience for one client, this manual process becomes a significant drag on your business as you scale. It’s a silent growth killer that operates in four distinct ways:
-
It’s a Time Sink: Each new client might take 15-20 minutes of administrative setup. That doesn’t sound like much, but with four new clients a month, you’re losing over an hour—an hour you could have spent on billable work, business development, or refining your service offerings. Over a year, that’s a full day and a half of your time dedicated to being a data entry clerk.
-
It’s Prone to Human Error: We’ve all done it. A typo in a name (
Jhoninstead ofJohn), a transposed number in a phone number, or an incorrect domain in an email address. These small mistakes can cascade into major headaches, from failed email communications and awkward corrections to bounced invoices and a damaged professional image.
-
It Creates a Scalability Ceiling: Your capacity to manually onboard clients is finite. This process creates a direct bottleneck. If you have a successful marketing campaign and five new clients want to sign on in the same week, your “time to value” for each of them is delayed by your own administrative backlog. You can’t effectively grow your client base if your foundational processes rely entirely on your own limited time and attention.
-
It Delivers a Poor First Impression: The modern client expects efficiency. A clunky, slow onboarding experience filled with requests for information they’ve already provided can create friction from day one. It subtly communicates that your internal processes aren’t as polished as your external-facing services, planting a small seed of doubt before the real work has even begun.
Introducing a Zero-Touch Client Data Workflow
Imagine a different scenario. The new client sends their “We’re ready to start!” email, complete with their contact details in the signature. Instead of you springing into action, an automated workflow does it for you.
Within moments, an intelligent system reads the email, identifies and extracts the key pieces of information—name, company, phone number, address—and populates a new record in your AMA Patient Referral and Anesthesia Management System client management app. You simply get a notification: “New Client: [Client Name] has been added.”
This is the promise of a zero-touch client data workflow. It’s a system designed to move information from its source (an email) to its final destination (your application) without any manual intervention from you. You shift from being the data transcriber to the overseer of an efficient, automated process. Your role becomes one of verification, not origination, freeing you to focus immediately on delivering value to your new client.
Overview of Our [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606) Stack
To build this zero-touch workflow, we don’t need a complex or expensive suite of enterprise software. We can achieve it by intelligently connecting a few powerful tools, most of which you may already be using. Here’s a high-level look at the components we’ll be wiring together in this tutorial:
-
Trigger: Gmail. This is our starting line. The entire process will kick off when a new email with specific criteria (e.g., from a new client, with a specific subject line, or moved to a specific label) arrives in your inbox.
-
Orchestrator: [AI Powered Cover Letter Automated Quote Generation and Delivery System for Jobber Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automated Work Order Processing for UPS-Engine-p111092). This is the powerful “glue” that holds our entire workflow together. Running directly within your Google account, this JavaScript-based platform will be our central controller. It will fetch the email content, communicate with our AI model, and then write the structured data to our database.
-
The Brains: Google Gemini API. This is the magic ingredient. We will send the raw, unstructured text from the client’s email body to the Gemini model. We’ll instruct it to act as an expert data extractor, asking it to find and return the client’s contact information in a clean, predictable JSON format.
-
Database & App: [Automated Web Scraping with [Multilingual Text-to-Speech Tool with SocialSheet Streamline Your Social Media Posting 123](https://votuduc.com/Multilingual-Text-to-Speech-Tool-with-Google-Workspace-p809282)](https://votuduc.com/Automated-Web-Scraping-with-Google-Sheets-p292968) & AppSheetway Connect Suite. This is our destination. Google Sheets will serve as our simple, reliable, and easily accessible database to store the client data. OSD App Clinical Trial Management sits on top, instantly transforming that spreadsheet into a polished, powerful, and mobile-friendly application for you to manage client relationships, track projects, and run your business.
Our data will flow in a clear, linear path: Gmail → Genesis Engine AI Powered Content to Video Production Pipeline → Gemini API → Google Sheets → AppSheet. In the following sections, we’ll dive into configuring each piece of this stack to build our automated onboarding machine.
Architecting the Solution: A High-Level Blueprint
Before we start slinging code, let’s step back and look at the architecture. A well-designed plan is the difference between a brittle, one-off script and a robust, scalable automation. Understanding how the pieces fit together and what role each component plays is crucial for building and, more importantly, for troubleshooting later.
Visualizing the Data Flow: from Gmail to AppSheet
At its core, our automation is a data processing pipeline. It picks up unstructured information from one place (an email), transforms it into structured data, and delivers it to another (our AppSheet app).
Here’s the step-by-step journey our data will take:
-
Trigger Event: A new client inquiry email lands in a specific folder or label in your Gmail inbox (e.g., “New Leads”).
-
Scheduled Execution: A time-driven trigger in [Architecting Multi Tenant AI Workflows in Building Modular Agentic Apps Script with Gemini Function Calling](https://votuduc.com/architecting-multi-tenant-ai-workflows-in-google-apps-script-p-20260321290501) runs periodically (e.g., every 5 minutes). It’s our vigilant lookout, constantly checking for new work to do.
-
Email Ingestion: The Apps Script scans the designated Gmail label for unread emails.
-
Content Extraction: For each new email, the script extracts the relevant content—the email body. This is our raw, unstructured data.
-
AI-Powered Parsing: The script sends this raw text to the Gemini API with a carefully crafted prompt. The prompt instructs Gemini to act as a data entry assistant, identifying and extracting key pieces of information like the client’s name, company, contact details, and a summary of their request.
-
Structured Data Return: Gemini processes the text and returns the extracted information in a clean, predictable JSON format. This is the magic moment where chaos becomes order.
-
API Payload Creation: Our Apps Script receives the JSON from Gemini and transforms it into the specific format required by the AppSheet API. This involves mapping the extracted data (e.g., “clientName”) to the corresponding columns in our AppSheet table.
-
Data Insertion: The script makes a secure HTTP POST request to the AppSheet API, sending the formatted data as the payload. The AppSheet API receives the request and adds a new row to your app’s underlying data source (e.g., a Google Sheet).
-
Cleanup: To avoid processing the same email twice, the script marks the email as read or moves it to an “Processed” label in Gmail. This completes the cycle.
This flow creates a seamless, “hands-off” bridge directly from your inbox to your business application.
The Role of Each Component: Apps Script, Gemini, and the AppSheet API
This automation is a team effort, with each technology playing a distinct and vital role. Let’s meet the players.
- Automating Technical Debt Audits in Apps Script with AI Agents (The Conductor):
Think of Apps Script as the central nervous system or the conductor of our orchestra. It’s the “glue” that holds the entire workflow together. Written in JavaScript and hosted on Google’s infrastructure, its job is to:
-
Orchestrate: Manage the timing and sequence of operations using time-driven triggers.
-
Connect: Use native services like
GmailAppto read your emails andUrlFetchAppto communicate with external APIs (Gemini and AppSheet). -
Process: Handle all the logic—looping through emails, parsing JSON responses, building API requests, and implementing error handling.
-
Gemini API (The Interpreter):
This is the AI brain of the operation. Gemini’s strength lies in its advanced natural language understanding. We’re moving beyond simple keyword matching and into true comprehension. Its role is to:
-
Understand Context: Read the free-form text of an email and understand the intent behind it.
-
Extract Entities: Pinpoint and pull out specific pieces of information (names, dates, phone numbers, project descriptions) from the noise.
-
Structure Data: Convert the unstructured prose of an email into a structured, machine-readable JSON object that our script can easily work with. It effectively translates human language into application data.
-
AppSheet API (The Gatekeeper):
The AppSheet API is the official, secure front door to your application’s data. You never want to manipulate the underlying Google Sheet directly, as that bypasses all the logic, security, and validation rules you’ve built into your app. The API’s role is to:
-
Provide an Endpoint: Offer a stable REST API endpoint to
ADDrecords to your tables. -
Enforce Rules: Ensure that any data coming in adheres to the column types, validation rules (
Valid_Ifconstraints), and security filters defined within your AppSheet application. -
Abstract the Data Source: It doesn’t matter if your data is in Google Sheets, SQL, or another source; the API provides a consistent way to interact with it, making your automation more resilient to backend changes.
Prerequisites: What You Need Before You Start
Before you can build this workflow, you’ll need to have a few things set up and ready to go. Getting these in order now will save you a lot of headaches later.
-
Automatically create new folders in Google Drive, generate templates in new folders, fill out text automatically in new files, and save info in Google Sheets Account: A standard
@gmail.comaccount will work, but a AC2F Streamline Your Google Drive Workflow account is recommended for business use cases. This gives you access to Gmail, Google Sheets, and Google Apps Script. -
An AppSheet App: You need an existing client onboarding app. The key is that it must have a table (backed by a Google Sheet or other data source) with columns ready to receive the data we’ll be extracting, such as
ClientName,ContactEmail,PhoneNumber,ProjectSummary, and aStatuscolumn. -
AppSheet API Access:
-
Your AppSheet plan must include API access (typically the Core plan or higher).
You must enable the API for your application and generate an* Application Access Key**. You can find this in your AppSheet account under My Account > Integrations > IN: from cloud services to AppSheet.
-
Google Cloud Platform (GCP) Project:
-
You need a GCP project with billing enabled to use the Gemini API.
In your GCP project, you must enable the* [Building Self Correcting Agentic Workflows with Building Self-Correcting Agentic Workflows with Vertex AI](https://votuduc.com/building-self-correcting-agentic-workflows-with-vertex-ai-p-20260321542526) API**.
-
You’ll need a way to authenticate your requests from Apps Script to the Gemini API. We’ll be using Apps Script’s built-in OAuth2 capabilities, which simplifies this process significantly.
-
Basic Technical Familiarity: While we’ll walk through every step, a foundational understanding of the following will be very helpful:
-
JavaScript: The language used for Google Apps Script.
-
APIs: A conceptual understanding of what an API is, including concepts like HTTP requests (GET, POST), headers, and JSON payloads.
Step 1: Capturing Client Inquiries with Google Apps Script
Our automation journey begins at the source: the client’s email inquiry. To intercept and process these emails programmatically, we’ll leverage Google Apps Script. It’s the native, serverless JavaScript platform built into Automated Client Onboarding with Google Forms and Google Drive., giving us direct, authenticated access to services like Gmail without the complexity of managing external servers or OAuth 2.0 flows. Think of it as the connective tissue that will grab our raw material—the email—and prepare it for the next stage.
Setting Up Your Apps Script Project
First, we need a place to write and host our code. A standalone Apps Script project is perfect for this task.
-
Navigate to the Apps Script Dashboard: Open your browser and go to
script.google.com. -
Create a New Project: In the top-left corner, click the + New project button.
-
Name Your Project: The editor will open with an
Untitled project. Click on the name and give it something descriptive, like “Gemini Client Onboarding”. This helps you identify it later. -
Enable the Gmail API: Our script needs explicit permission to interact with Gmail.
In the left-hand menu, next to “Services”, click the* +** icon.
A list of available Automated Discount Code Management System APIs will appear. Scroll down and select* Gmail API**.
Click* Add**. You will now see Gmail listed under Services, confirming that your script can now use the GmailApp service.
You’ll be working primarily in the Code.gs file shown in the editor. This is where we’ll write the logic to fetch and parse our emails.
Configuring a Gmail Trigger to Watch for New Emails
Our script won’t do anything until we tell it when to run. We need it to check for new client inquiries automatically. While Gmail doesn’t have a direct “on new email” push event for Apps Script, the standard and most reliable method is to use a time-driven trigger that runs our script at a regular interval.
-
Open the Triggers Menu: In the left-hand menu of the Apps Script editor, click the clock icon labeled Triggers.
-
Add a New Trigger: In the bottom-right corner, click the + Add Trigger button.
-
Configure the Trigger Settings: A configuration window will pop up. Set it up as follows:
-
Choose which function to run: We haven’t written it yet, but we’ll name our main function
processNewInquiries. Select that name from the dropdown. -
Choose which deployment should run: Leave this as
Head. -
Select event source: Change this from the default to
Time-driven. -
Select type of time-based trigger: Choose
Minutes timer. -
Select minute interval: Select
Every 5 minutesorEvery 10 minutes. This determines how frequently the script checks for new mail. A 5-minute interval is a good starting point for responsive onboarding.
- Save the Trigger: Click Save.
The first time you save a trigger, Google will prompt you to authorize the script. You’ll need to choose your Google account and approve the permissions it requests (like “Read, compose, send, and permanently delete all your email from Gmail”). This is a standard security step to grant your script the ability to perform its duties.
Parsing the Raw Email Body for Processing
Now for the core logic. We’ll write the processNewInquiries function that our trigger is set to run. This function will search for specific unread emails, extract their content, and log it for now.
Before you write the code, it’s a best practice to create a dedicated label in Gmail for your incoming inquiries, such as client-inquiries. Have your contact form or direct email instructions filter these messages with this label. This makes your script’s search query far more efficient and reliable.
Replace the default myFunction in your Code.gs file with the following code:
/**
* Searches for new client inquiry emails, extracts their content,
* and prepares them for further processing.
*/
function processNewInquiries() {
// Best Practice: Use a dedicated label to isolate inquiry emails.
// This query finds all unread emails with the 'client-inquiries' label
// and excludes any Google Chat notifications.
const searchQuery = "is:unread label:client-inquiries -is:chat";
// GmailApp.search() returns an array of threads.
const threads = GmailApp.search(searchQuery);
Logger.log(`Found ${threads.length} new inquiry thread(s).`);
// Iterate over each email thread.
for (const thread of threads) {
// Get all messages in the thread (we usually care about the first one).
const messages = thread.getMessages();
// We'll process the first unread message in the thread.
const message = messages[0];
// Extract the essential information from the message.
const inquiryData = {
id: message.getId(),
date: message.getDate(),
from: message.getFrom(),
subject: message.getSubject(),
// getPlainBody() is crucial. It strips HTML, providing clean text
// which is ideal for processing by an LLM like Gemini.
body: message.getPlainBody()
};
// For now, we'll just log the structured data.
// In the next step, we will send this object to the Gemini API.
Logger.log("--- New Inquiry Data ---");
Logger.log(JSON.stringify(inquiryData, null, 2));
// CRITICAL: Mark the thread as read to prevent reprocessing.
// This is the most important step for state management.
thread.markRead();
// Optional: You could also add a 'processed' label for easier tracking.
// const processedLabel = GmailApp.getUserLabelByName('processed');
// if (processedLabel) {
// thread.addLabel(processedLabel);
// }
}
}
Breaking Down the Code:
-
searchQuery: This is the key to finding the right emails. We combineis:unreadwith our customlabel:client-inquiriesto ensure we only grab new, relevant messages. -
GmailApp.search(searchQuery): This command executes the search and returns an array ofGmailThreadobjects. -
Looping and Extracting: We loop through each thread, grab the first message, and use methods like
.getSubject()and.getFrom()to pull out the metadata. -
getPlainBody(): This is a vital choice. Client emails can contain complex HTML signatures, formatting, and images.getPlainBody()strips all of that away, leaving us with the raw text content—the perfect input for an AI model. -
Logger.log(): This is your best friend for debugging. After running the function manually (by clicking the Run button in the editor), you can view the output in the Execution log to see the data you’ve captured. -
thread.markRead(): This is the most critical line for preventing infinite loops. By marking the thread as read, ouris:unreadsearch query will not find it on the next run, ensuring each inquiry is processed only once.
With this script in place and the trigger active, you now have a robust mechanism that automatically captures new client inquiries every few minutes and structures their content, ready to be handed off to Gemini for intelligent analysis.
Step 2: Intelligent Data Extraction with Gemini
This is where the magic happens. Traditional automation often breaks down when faced with the unstructured, conversational nature of an email. A client might say “we need this by the end of next week,” or “the project is a ‘Q3 Marketing Push’.” Simple keyword searches or regular expressions are brittle and would fail spectacularly.
Enter Gemini 1.5 Pro. We’re not just searching for text; we’re asking a powerful reasoning engine to understand the email’s content and intent, then structure the key information for us. This leap from simple parsing to contextual understanding is what makes this automation robust and scalable.
Connecting to the Gemini 1.5 Pro API
First things first, we need to establish a connection to the model. We’ll do this through the Vertex AI SDK for JSON-to-Video Automated Rendering Engine, which provides a clean and straightforward way to interact with Google’s AI models. This code would typically live inside a Google Cloud Function that gets triggered by the new email.
Prerequisites:
-
A Google Cloud Project with billing enabled.
-
The Vertex AI API enabled in your project.
-
Authentication configured for your environment (e.g., by running
gcloud auth application-default loginin your terminal).
Here’s how you initialize the model in your Python script:
import vertexai
from vertexai.generative_models import GenerativeModel, Part
import json
# TODO: Replace with your project ID and location
PROJECT_ID = "your-gcp-project-id"
LOCATION = "us-central1" # e.g., us-central1
# Initialize the Vertex AI SDK
vertexai.init(project=PROJECT_ID, location=LOCATION)
# Load the Gemini 1.5 Pro model
# This model is excellent for its large context window and advanced reasoning
model = GenerativeModel("gemini-1.5-pro-001")
print("Successfully connected to Gemini 1.5 Pro.")
This snippet handles the foundational work. It imports the necessary libraries, points the SDK to your specific project, and creates an instance of the gemini-1.5-pro-001 model, making it ready to receive our prompts.
Crafting the Perfect Prompt for Entity Extraction
The quality of your output is directly proportional to the quality of your prompt. A lazy prompt gets a lazy answer. For a task like entity extraction, we need to be precise, providing clear instructions, a defined schema, and examples. This technique, known as “few-shot prompting,” dramatically improves the model’s accuracy and consistency.
Our prompt will instruct Gemini to act as a data extraction specialist, define the exact JSON structure we need, and provide an example of how to handle a sample email.
Here is a robust, production-ready prompt template:
# The email content from Step 1 will be injected into this prompt
email_body = """
Hello Team,
We're excited to kick off a new project. Our client is MegaCorp Inc.
and the main contact is Jane Doe ([email protected]).
The project is a complete "Website Redesign". We need to have the
initial mockups ready for review by October 25, 2024.
Please let me know who will be leading this.
Best,
John
"""
# This is our master prompt template
prompt = f"""
You are an expert data extraction assistant. Your task is to analyze an email from a new client and extract key onboarding information into a structured JSON format.
**Instructions:**
1. Carefully read the entire email content provided.
2. Extract the entities listed in the JSON schema below.
3. If a specific piece of information cannot be found, use `null` as the value for that key.
4. The `project_deadline` should be in "YYYY-MM-DD" format. Infer the date from relative terms like "next Friday" if possible, assuming today's date is 2024-10-15.
5. Respond ONLY with a single, valid JSON object. Do not include any explanatory text, markdown formatting, or anything else before or after the JSON.
**JSON Schema:**
{{
"client_name": "string",
"company_name": "string",
"contact_email": "string",
"project_type": "string",
"project_deadline": "string (YYYY-MM-DD format)",
"summary": "string (A brief, one-sentence summary of the request)"
}}
**Example:**
**Email Input:**
"Hi, this is Bob from Acme Solutions. We need a new mobile app developed for our logistics department. The primary contact will be [email protected]. We're hoping to get this done by the end of the year."
**JSON Output:**
{{
"client_name": "Bob",
"company_name": "Acme Solutions",
"contact_email": "[email protected]",
"project_type": "Mobile App Development",
"project_deadline": "2024-12-31",
"summary": "Client requests a new mobile app for their logistics department with a deadline by the end of the year."
}}
**Now, process the following email:**
**Email Input:**
{email_body}
**JSON Output:**
"""
This prompt is effective because it:
-
Assigns a Role: “You are an expert data extraction assistant.”
-
Gives Clear Rules: Explicitly states how to handle missing data (
null) and date formats. -
Defines the Schema: Provides the exact keys and expected data types.
-
Provides a High-Quality Example: Shows the model precisely what success looks like.
-
Demands a Specific Output Format: “Respond ONLY with a single, valid JSON object.” This is critical for reliable programmatic parsing.
Handling the Structured JSON Response from the AI
With our model connected and our prompt engineered, the final step is to send the request and parse the response. Because we’ve been so specific in our prompt, we can have a high degree of confidence that the model’s output will be a clean JSON string. However, we should always code defensively and wrap our JSON parsing in a try...except block.
def extract_client_data_from_email(email_content: str) -> dict:
"""
Uses Gemini to extract structured client data from an email body.
"""
# Construct the full prompt using the template and the new email content
prompt_template = f"""
You are an expert data extraction assistant...
... (Full prompt from above) ...
**Now, process the following email:**
**Email Input:**
{email_content}
**JSON Output:**
"""
try:
# Send the prompt to the model
response = model.generate_content(prompt_template)
# The raw response might have markdown backticks, so we clean it up
cleaned_response_text = response.text.strip().replace("```json", "").replace("```", "")
# Attempt to parse the cleaned text as JSON
extracted_data = json.loads(cleaned_response_text)
print("Successfully extracted data:")
print(json.dumps(extracted_data, indent=2))
return extracted_data
except json.JSONDecodeError as e:
print(f"Error: Failed to decode JSON from model response. Error: {e}")
print(f"Raw response was: {response.text}")
return None
except Exception as e:
print(f"An unexpected error occurred: {e}")
return None
# --- Example Usage ---
# The email_body would come from our email processing trigger (Step 1)
client_details = extract_client_data_from_email(email_body)
if client_details:
# Now we have a clean Python dictionary, ready for the next step!
# e.g., client_details['company_name'] will give us "MegaCorp Inc."
pass
This function encapsulates the entire logic. It sends the prompt, cleans up potential markdown formatting that the AI sometimes adds, and safely parses the string into a Python dictionary. If anything goes wrong—if the model responds with an error or malformed JSON—the except block catches it, logs the problem, and prevents our entire workflow from crashing.
We now have our client’s request transformed from an unstructured email into a perfectly structured dictionary, ready to be sent to AppSheet in the next step of our automation pipeline.
Step 3: Updating Your AppSheet CRM via API
With our structured client data extracted and validated by Gemini, it’s time for the final, satisfying step: programmatically adding this information to our AppSheet CRM. This is where the automation truly comes to life, bridging the gap between an unstructured email and a structured database entry without any manual intervention. We’ll use the AppSheet REST API, which provides a powerful and direct way to interact with your application’s data.
Enabling API Access in Your AppSheet Application
Before you can send data to your app, you need to explicitly grant programmatic access. AppSheet keeps this locked down by default for security, but enabling it is straightforward.
-
Navigate to the Integrations Pane: Open your application in the AppSheet editor. Go to the Manage section (the icon that looks like a little robot or monitor) on the left-hand navigation bar.
-
**Enable API Access: Click on the Integrations tab. You’ll see two sections:
IN: from cloud services to your appandOUT: from your app to cloud services. We are sending data in, so under theINsection, find the AppSheet API provider and click to open its settings. -
Toggle and Secure Your Keys: Inside the AppSheet API configuration, you’ll see a simple toggle to Enable access. Turn it on.
Once enabled, AppSheet will reveal two critical pieces of information:
-
Application ID: A unique identifier for your app.
-
Application Access Key: A secret token that acts as a password for your API.
Putting It All Together: A Complete Code Walkthrough
We’ve discussed the theory, the APIs, and the logic. Now, let’s assemble the engine of our automation. This section provides the complete, production-ready Google Apps Script code, explains how to tailor it to your specific setup, and guides you through deploying it so it can run automatically.
The Full Google Apps Script Code
Below is the entire script. Copy and paste this into your Google Apps Script editor. We’ve packed it with comments to explain what each part of the code is doing, from fetching emails to talking with the Gemini and AppSheet APIs.
/**
* =================================================================
* GLOBAL CONFIGURATION
* -----------------------------------------------------------------
* Update these variables with your specific keys, IDs, and names.
* =================================================================
*/
// Your Google AI Studio API Key for Gemini
const GEMINI_API_KEY = 'YOUR_GEMINI_API_KEY';
// Your AppSheet App ID (from the URL of the editor)
const APPSHEET_APP_ID = 'YOUR_APPSHEET_APP_ID';
// The exact name of the table in your AppSheet app to add clients to
const APPSHEET_TABLE_NAME = 'Clients'; // e.g., 'Clients', 'Onboarding', etc.
// Your AppSheet Access Key (generate this in your AppSheet account settings)
const APPSHEET_API_KEY = 'YOUR_APPSHEET_API_KEY';
// The Gmail label you created to trigger the automation
const GMAIL_LABEL_NAME = 'New Client Onboarding';
// The prompt for Gemini. This is crucial!
// Customize it to match the structure of your emails and the columns in your AppSheet table.
const GEMINI_PROMPT = `
Analyze the following email body from a new client. Extract the following details:
- CompanyName
- ContactPerson
- ContactEmail
- PhoneNumber
- ServicePackage (e.g., "Basic", "Pro", "Enterprise")
- ProjectStartDate (format as YYYY-MM-DD)
If a piece of information is not present, use "N/A" as the value.
Return the result ONLY as a single, minified JSON object with these exact keys. Do not include any other text, explanations, or markdown formatting.
Email Body:
`;
/**
* =================================================================
* MAIN FUNCTION
* -----------------------------------------------------------------
* This is the entry point for our automation trigger.
* =================================================================
*/
/**
* Processes unread emails with a specific label, extracts client data using Gemini,
* and adds the data as a new row in an AppSheet application.
*/
function processNewClientEmails() {
const label = GmailApp.getUserLabelByName(GMAIL_LABEL_NAME);
if (!label) {
console.error(`Gmail label "${GMAIL_LABEL_NAME}" not found. Please create it.`);
return;
}
const threads = label.getThreads(0, 5); // Process up to 5 threads at a time to avoid timeouts
for (const thread of threads) {
if (thread.isUnread()) {
const message = thread.getMessages()[0]; // Get the first message in the thread
const emailBody = message.getPlainBody();
try {
// Step 1: Extract and structure data with Gemini
const clientDataJson = getStructuredDataFromGemini(emailBody);
const clientData = JSON.parse(clientDataJson);
console.log(`Successfully parsed data from Gemini: ${JSON.stringify(clientData)}`);
// Step 2: Add the data to AppSheet
addClientToAppSheet(clientData);
console.log(`Successfully added client "${clientData.CompanyName}" to AppSheet.`);
// Step 3: Mark as processed
thread.markRead();
thread.moveToArchive();
console.log(`Processed and archived email thread with subject: "${message.getSubject()}"`);
} catch (error) {
console.error(`Failed to process email thread with subject: "${message.getSubject()}". Error: ${error.message}`);
// Optional: Add a "Processing Error" label to the email for manual review
}
}
}
}
/**
* =================================================================
* HELPER FUNCTIONS
* -----------------------------------------------------------------
* These functions handle the API calls and data processing.
* =================================================================
*/
/**
* Sends the email body to the Gemini API and returns the structured JSON data.
* @param {string} emailBody The plain text content of the email.
* @returns {string} A JSON string containing the extracted client data.
*/
function getStructuredDataFromGemini(emailBody) {
const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${GEMINI_API_KEY}`;
const payload = {
"contents": [{
"parts": [{
"text": GEMINI_PROMPT + emailBody
}]
}]
};
const options = {
'method': 'post',
'contentType': 'application/json',
'payload': JSON.stringify(payload),
'muteHttpExceptions': true // Important to catch errors manually
};
const response = UrlFetchApp.fetch(url, options);
const responseCode = response.getResponseCode();
const responseBody = response.getContentText();
if (responseCode === 200) {
const jsonResponse = JSON.parse(responseBody);
// Clean up the response to get just the JSON text
let extractedJson = jsonResponse.candidates[0].content.parts[0].text;
extractedJson = extractedJson.replace(/```json/g, '').replace(/```/g, '').trim();
return extractedJson;
} else {
throw new Error(`Gemini API Error: ${responseCode} - ${responseBody}`);
}
}
/**
* Sends the structured client data to the AppSheet API to add a new row.
* @param {object} clientData A JavaScript object with client details.
*/
function addClientToAppSheet(clientData) {
const url = `https://api.appsheet.com/api/v2/apps/${APPSHEET_APP_ID}/tables/${APPSHEET_TABLE_NAME}/Action`;
const payload = {
"Action": "Add",
"Properties": {
"Locale": "en-US"
},
"Rows": [
clientData // The object keys must match your AppSheet column names
]
};
const options = {
'method': 'post',
'contentType': 'application/json',
'headers': {
'ApplicationAccessKey': APPSHEET_API_KEY
},
'payload': JSON.stringify(payload),
'muteHttpExceptions': true
};
const response = UrlFetchApp.fetch(url, options);
const responseCode = response.getResponseCode();
const responseBody = response.getContentText();
if (responseCode !== 200) {
throw new Error(`AppSheet API Error: ${responseCode} - ${responseBody}`);
}
}
How to Customize Key Variables for Your Specific Needs
The script won’t work out of the box. You need to replace the placeholder values in the “GLOBAL CONFIGURATION” section at the top. This is the most important step to making the script your own.
Here’s a breakdown of each variable and where to find its value:
-
GEMINI_API_KEY: -
What it is: Your secret key to authenticate with the Gemini API.
-
How to get it: Go to Google AI Studio. Create a new API key in your Google Cloud project. Treat this like a password; don’t share it publicly.
-
APPSHEET_APP_ID: -
What it is: The unique identifier for your AppSheet application.
-
**How to get it: Open your app in the AppSheet editor. Look at the URL in your browser’s address bar. It will look something like
https://www.appsheet.com/template/show?appId=.... The App ID is the long string of characters afterappId=. -
APPSHEET_TABLE_NAME: -
What it is: The name of the specific table (or sheet) within your AppSheet app where you want to add the new client data.
-
**How to get it: In the AppSheet editor, go to the “Data” tab. The name will be listed there. It must be an exact match, including capitalization and spacing (e.g.,
Clients, notclients). -
APPSHEET_API_KEY: -
What it is: Your secret key to authenticate with the AppSheet API. This is different from the App ID.
-
How to get it: In the AppSheet editor, go to Manage > Integrations > IN: from cloud services and apps. Make sure “Enable” is checked. You’ll see an “Application Access Key” section. Click “Create Application Access Key” to generate one.
-
GMAIL_LABEL_NAME: -
What it is: The label you’ll apply to emails in Gmail that you want the script to process.
-
**How to get it: You create this yourself in Gmail. Go to Gmail, create a new label (e.g., “New Client Onboarding”), and make sure the name you use here matches the script variable exactly.
-
GEMINI_PROMPT: -
What it is: This is the brain of the operation. It’s the set of instructions you give to Gemini to tell it how to parse the email and what data to extract.
-
How to customize it: This is critical. Look at the
GEMINI_PROMPTin the code. You must update the list of details (CompanyName,ContactPerson, etc.) to match the column names in your AppSheet table. If your emails have a different structure, you may need to adjust the instructional text to help Gemini find the right information more reliably.
Deploying and Testing Your Automated Workflow
Once your code is in the editor and your variables are customized, it’s time to bring your automation to life.
- Save Your Project:
- In the Apps Script editor, click the floppy disk icon (Save project) at the top. Give your project a memorable name, like “Gmail to AppSheet Onboarding”.
- Initial Authorization and Manual Test:
-
Before setting up an automatic trigger, run the script once manually to grant the necessary permissions.
-
Find an email you want to process, apply your new Gmail label (
New Client Onboarding), and make sure it’s marked as unread. -
In the Apps Script editor, select the
processNewClientEmailsfunction from the dropdown menu next to the “Debug” button.
Click* Run**.
A popup will appear asking for authorization. You’ll need to grant the script permission to access your Gmail and to connect to external services (for the API calls). Review the permissions and click* Allow**.
The script will execute. Check the* Execution log** at the bottom of the editor for any errors. If it was successful, you should see a new row in your AppSheet app!
- Set Up the Automated Trigger:
- This is the final step to make the process hands-off. We’ll create a trigger that runs the script automatically every few minutes.
In the Apps Script editor, click on the* Triggers** icon (it looks like a clock) in the left-hand sidebar.
Click the* + Add Trigger** button in the bottom-right corner.
-
Configure the trigger with the following settings:
-
Choose which function to run:
processNewClientEmails -
Choose which deployment should run:
Head -
Select event source:
Time-driven -
Select type of time-based trigger:
Minutes timer -
Select minute interval:
Every 10 minutesorEvery 15 minutesis a good starting point.
Click* Save**.
Your workflow is now live! Every 10-15 minutes, the script will automatically wake up, scan for newly labeled emails, process them through Gemini, add them to AppSheet, and clean up after itself by archiving the email. You can monitor its performance by checking the “Executions” tab in the Apps Script editor.
Conclusion: Scale Your Operations, Not Your Admin Time
The architecture we’ve detailed isn’t just a clever integration; it’s a fundamental shift in how you approach operational growth. By bridging the unstructured world of email with the structured environment of AppSheet using the intelligence of Gemini, you dismantle the linear relationship between business growth and administrative overhead. You’ve moved beyond a process that requires manual intervention for every new client and into a system that scales autonomously. The result is a workflow that empowers your team to focus on high-value client relationships and strategic initiatives, rather than being bogged down by the repetitive, error-prone task of data entry. This is the core principle of modern automation: building systems that work for you, allowing you to scale your impact without scaling your headcount.
The Tangible ROI of Automated Client Management
The return on investment for a system like this manifests across several critical business dimensions. While the immediate benefit is reclaimed time, the downstream effects are even more profound.
-
Drastic Reduction in Manual Labor: Eliminating the need to manually parse emails and enter data can save dozens, if not hundreds, of hours per month, depending on your client volume. This directly translates to reduced operational costs or, more powerfully, reallocated human capital towards revenue-generating activities.
-
Enhanced Data Integrity: Automation eradicates the typos, omissions, and inconsistencies inherent in manual data entry. The data entering your AppSheet application is clean, structured, and reliable from the outset, which prevents costly downstream errors in billing, project management, and client communication.
-
Accelerated Onboarding Velocity: The time from a client’s initial email to their official entry into your system shrinks from hours or days to mere seconds. This speed creates an exceptional first impression, demonstrates efficiency, and shortens the sales-to-service cycle, improving cash flow and client satisfaction.
-
Scalable Throughput: Your capacity to onboard new clients is no longer limited by your team’s administrative bandwidth. Whether you receive five new client emails a day or fifty, the system processes them with the same speed and accuracy, enabling true business scalability.
Beyond Onboarding: Other Potential Use Cases
The email-parsing, data-structuring pattern we’ve implemented is a versatile and powerful blueprint that can be adapted to automate numerous other business processes. Think of this as a foundational capability, not a single-purpose solution. Consider these possibilities:
-
Automated Support Ticket Triage: Configure the system to monitor a support inbox. Gemini can analyze incoming emails to categorize the issue (e.g., “Billing Inquiry,” “Technical Bug,” “Feature Request”), assess its urgency, and create a pre-populated ticket in an AppSheet-based helpdesk app, assigning it to the correct team automatically.
-
Intelligent Lead Routing: Point the automation at your “[email protected]” inbox. Gemini can extract contact details, summarize the nature of the inquiry from the email body, and qualify the lead based on predefined criteria. The structured data is then used to create a new record in your AppSheet CRM, instantly notifying the appropriate sales representative.
-
Vendor Invoice Processing: Automate your accounts payable workflow by having Gemini extract key information—vendor name, invoice number, due date, and total amount—from PDF invoices sent to an AP email address. This data can then populate an AppSheet app for approval and payment tracking.
-
Unstructured Feedback Analysis: Funnel customer feedback from emails, surveys, or contact forms into the system. Use Gemini’s natural language capabilities for How to build a Custom Sentiment Analysis System for Operations Feedback Using Google Forms AppSheet and Vertex AI and topic extraction, then push the structured results to an Building an AI Powered Business Insights Dashboard with AppSheet and Looker Studio to visualize trends and gain actionable insights into customer satisfaction.
Ready to Scale Your Architecture? Book a GDE Discovery Call
While this guide provides a powerful template, deploying a production-grade automation solution tailored to your unique operational complexities requires strategic planning. Your email formats, business logic, and integration points have specific nuances that can be addressed with expert architecture.
If you are ready to move from concept to implementation and build a robust, scalable automation engine for your business, we invite you to book a discovery call. In this session, a Google Developer Expert (GDE) will work with you to understand your specific challenges, map your existing workflows, and outline a custom solution that leverages the full potential of Google Cloud, Gemini, and AppSheet.
**[Book Your Complimentary GDE Discovery Call Today]**This is your opportunity to transform a critical business bottleneck into a competitive advantage. By automating the bridge between client communication and your operational data, you’re not just saving time—you’re building a more resilient, responsive, and scalable organization ready for the future. Don’t let manual processes dictate the pace of your growth. Take the first step towards intelligent automation and unlock your company’s true potential.
Stop Doing Manual Work. Scale with AI.
Want to turn these blog concepts into production-ready reality for your team?
Table Of Contents
Portfolios
Related Posts
Quick Links
Legal Stuff