Google Sheets

Use the Google Sheets node in the SMSPort Visual Bot Builder to automate work in Google Sheets and integrate your spreadsheets with customer WhatsApp conversations. SMSPort has built-in support for a wide range of Google Sheets operations, including creating, updating, appending, removing, and reading spreadsheet rows.

On this page, you will find a list of operations the Google Sheets node supports, step-by-step inspector field instructions, and practical examples.

[!NOTE]
Credentials

Refer to Google Sheets Credentials for guidance on setting up OAuth2 authentication or Google Cloud Service Account permissions.

Operations Supported

Document Operations

Create Spreadsheet: Automatically initialize a new Google Spreadsheet for a new tenant or campaign.
Delete Spreadsheet: Remove an archived spreadsheet.

Sheet Within Document Operations

Append or Update Row: Append a new row, or update the existing row if the primary key (e.g. Phone or MRN) already exists.
Append Row: Add a new row to the bottom of the active sheet (ideal for capturing leads, consultation intakes, and feedback).
Clear Sheet: Clear all data rows from a sheet while preserving column headers in Row 1.
Create Sheet: Add a new named tab inside an existing spreadsheet document.
Delete Sheet: Remove a specific tab from a spreadsheet.
Delete Rows or Columns: Remove obsolete records by row index or key match.
Get Row(s): Look up and retrieve one or multiple matching rows by column key (e.g. look up doctor schedule, patient MRN, or SKU inventory).
Update Row: Modify values in specific columns for a matched row (e.g. update appointment status from "Pending" to "Confirmed").

Screen Fields: What Data to Fill in the Inspector

When you click on a Google Sheets card in the Visual Bot Builder canvas, the right-side property inspector displays the following configuration fields:

Field Name on Screen	Description	Required	What Data to Fill / Real Example
Credential	Connected Google OAuth Account	Yes	Select your authorized account from the dropdown (e.g. `Clinic Main Google Account`).
Document ID	Spreadsheet Document ID	Yes	Copy the unique alphanumeric string from your Google Sheet URL between `/d/` and `/edit`: `1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms`
Sheet Name	Tab Name	Yes	Exact name of the tab at the bottom of the spreadsheet (e.g. `Appointments`, `IntakeForm`, `Sheet1`). Case-sensitive.
Operation	Action to perform	Yes	Choose `get_rows`, `append_row`, or `update_row`.
Lookup Variable Key	Search value variable	For `get_rows`	The bot variable holding the search query: `{{contact.phone}}` or `{{patient_mrn}}`.
Key Column	Row 1 Header to match	For `get_rows`	Exact header in your sheet (e.g. `Phone`, `MRN`, `Email`).
Result Column	Row 1 Header to fetch	For `get_rows`	The column value you want to extract: `DoctorName`, `Status`, `InvoiceAmount`.
Output Variable	Target memory variable	For `get_rows`	The name where the fetched value is stored: `assigned_doctor`.
Column Mappings (JSON)	Payload object	For `append_row`	JSON mapping column headers to bot variables: `{"Patient": "{{patient_name}}", "Phone": "{{contact.phone}}", "Status": "Active"}`

Templates and Examples

Here are common pre-built workflow patterns using the Google Sheets node:

Example 1: Patient Appointment Verification (Lookup Flow)

graph TD
    UserMsg([Patient: 'Check my appointment']) --> AskPhone[Ask Text: Confirm Phone Number]
    AskPhone --> SheetCheck[Google Sheets: Get Row where Phone == {{contact.phone}}]
    SheetCheck -->|success| ReplyFound[Send Text: Hello {{patient_name}}, your appointment with {{doctor_name}} is confirmed for {{slot_time}}.]
    SheetCheck -->|not_found| ReplyNew[Ask Button: No record found. Would you like to book now?]
    SheetCheck -->|error| Handoff[Human Handoff: Connecting to front desk...]

Example 2: In-Chat Lead Capture & Google Sheet Append

When a customer answers your qualification questions on WhatsApp:

Card 1: Ask Text -> captures patient_name.
Card 2: Ask Button -> captures department.

Card 3: Google Sheets (Append Row):

{
  "Timestamp": "{{system.timestamp}}",
  "Full Name": "{{patient_name}}",
  "Phone Number": "{{contact.phone}}",
  "Department": "{{department}}",
  "Status": "New Lead"
}

Card 4: Send Text -> "Thank you {{patient_name}}! Our intake coordinator has received your details."

Common Issues & Troubleshooting

1. `PERMISSION_DENIED` or `403 Forbidden`

Symptom: Bot encounters error branch during sheet operation.
Cause: The Google Sheet is private and has not been shared with the authorized Google OAuth account or Google Cloud Service Account email.
Solution: Open your spreadsheet in Google Drive, click the blue Share button in the top right, and add your connected account email with Editor permissions.

2. `HEADER_NOT_FOUND`

Symptom: Key column or result column returns empty data.
Cause: Row 1 in your Google Sheet has trailing spaces, special characters, or is empty.
Solution: Ensure the first row of your sheet contains clean header labels (e.g. use Phone instead of Phone Number (WhatsApp)).

3. `EXPRESSION_EVALUATION_ERROR`

Symptom: Variable value is written as literal {{patient_name}} into the spreadsheet.
Cause: Variable tag was misspelled or previous question node had outputVariable named differently.
Solution: Verify the preceding node's inspector property. Variable names are case-sensitive.

What to Do If Your Operation Isn't Supported

If this node doesn't natively support a specialized Google Workspace operation you require (such as generating Google Slides charts or creating Google Drive permissions), you can use the HTTP Request Node to call the Google REST API directly:

On the canvas, drag an HTTP Request card.
Set the HTTP Method to POST or PATCH.
Set the URL to https://sheets.googleapis.com/v4/spreadsheets/{{documentId}}/....
In Authentication, select Predefined Credential Type > Google OAuth2.
Select your connected credential to automatically inject Bearer tokens into the authorization header.