Transform hotel guest feedback with GPT-4 sentiment analysis & service recovery
Transform guest complaints into loyalty opportunities - achieving 60% reduction in negative reviews, 85% faster service recovery, and turning dissatisfied guests into brand advocates through AI-powered sentiment analysis and automated response workflows.
What This Workflow Does
Revolutionizes hotel guest experience management with AI-driven sentiment analysis and proactive service recovery:
📝 Real-Time Feedback Capture - Jotform collects guest feedback during their stay, not after checkout
🤖 AI Sentiment Analysis - GPT-4 analyzes feedback across sentiment, urgency, and reputation impact
🚨 Instant Escalation - Critical issues immediately alert managers via email and Slack
💝 Personalized Recovery Offers - AI generates custom compensation (upgrades, discounts, credits)
📧 Automated Guest Communication - Sends recovery offers to unhappy guests, thank-you notes to satisfied ones
🎫 PMS Integration - Creates tickets in your Property Management System with full context
⭐ Review Generation - Encourages happy guests to share experiences on Google, TripAdvisor, Booking.com
📊 Complete Analytics - Tracks all feedback with sentiment scores for trend analysis
🎯 Proactive Prevention - Resolves issues before guests post negative online reviews
💰 ROI Tracking - Measures service recovery effectiveness and guest satisfaction improvements
Key Features
AI Guest Experience Analyst: GPT-4 analyzes feedback across 10+ dimensions including sentiment scoring, urgency classification, and reputation impact assessment
Intelligent Routing: Automatically escalates critical/high-urgency issues to hotel management within minutes
Personalized Service Recovery: AI generates tailored compensation offers based on issue severity, guest emotional state, and long-term value potential
Multi-Channel Alerts: Instant notifications via Gmail and Slack ensure no critical feedback is missed
Sentiment Scoring: 0-100 numerical sentiment scores enable data-driven trend analysis
Category Detection: AI identifies issue categories (cleanliness, staff, amenities, noise, etc.)
Reputation Impact Assessment: Predicts likelihood of negative online review (low/medium/high)
Recovery Action Suggestions: AI recommends specific steps to resolve each guest concern
Positive Feedback Amplification: Automatically requests online reviews from satisfied guests with incentivized return offers
Property Management Integration: Creates structured tickets with all AI insights for staff follow-up
Complete Audit Trail: Google Sheets logging enables performance tracking and staff training insights
Cost Optimization: AI balances recovery offer value against long-term guest lifetime value
Perfect For
Boutique Hotels: 20-100 rooms requiring personalized guest experience management
Hotel Chains: Multi-property operations standardizing service recovery protocols
Resorts: Large properties with multiple service areas (spa, dining, housekeeping, etc.)
Business Hotels: Corporate-focused properties prioritizing fast issue resolution
Vacation Rentals: Airbnb management companies handling guest communications at scale
Hostels: Budget accommodations building reputation through responsive service
Extended Stay Properties: Long-term guest relationships requiring proactive care
Conference Centers: Event venues managing large groups and critical feedback
What You'll Need
Required Integrations
Jotform - Guest feedback form (free tier works)
Create your form for free on Jotform using this link
OpenAI API - GPT-4 for AI sentiment analysis (~$0.10-0.30 per feedback)
Gmail - Automated notifications to managers and guests
Google Sheets - Feedback database and analytics dashboard
Optional Integrations
Slack - Real-time alerts to management team
Property Management System - Automated ticket creation (via API)
Quick Start
- Import Template - Copy JSON and import into n8n
- Add OpenAI Credentials - Set up OpenAI API key (GPT-4 recommended for best results)
- Create Jotform Guest Feedback Form:
- Guest Name (q3_guestName)
- Guest Email (q4_guestEmail)
- Room Number (q5_roomNumber)
- Stay Dates (q6_stayDates)
- Overall Rating 1-5 (q7_overallRating)
- Feedback Comments (q8_feedbackComments)
- Service Area (q9_serviceArea) Create your form for free on Jotform using this link
- Configure Gmail - Add Gmail OAuth2 credentials (same credential for all 3 Gmail nodes)
- Setup Google Sheets:
- Create spreadsheet with "Guest Feedback Analytics" sheet
- Replace
YOUR_GOOGLE_SHEET_IDin workflow - Columns: timestamp, submissionId, guestName, roomNumber, stayDates, overallRating, serviceArea, sentiment, sentimentScore, urgencyLevel, keyIssues, categories, reputationImpact, recoveryOfferSent, feedbackText
- Configure PMS Integration (Optional):
- Add your PMS API endpoint URL
- Set up HTTP authentication credentials
- Setup Slack Webhook (Optional):
- Create Slack incoming webhook
- Replace
YOUR/SLACK/WEBHOOKin workflow
- Customize Email Addresses:
- Update
hotel.manager@yourhotel.com - Update
guestrelations@yourhotel.com - Update review site URLs in positive feedback email
- Update
- Test Workflow - Submit test feedback through Jotform
- Go Live - Share feedback form link with guests (QR codes in rooms, checkout emails, etc.)
Customization Options
Service Recovery Tiers: Adjust compensation levels based on issue severity and guest value
Auto-Approval Thresholds: Set limits for automatic vs manager-approved recovery offers
AI Prompt Tuning: Customize sentiment analysis criteria for your brand standards
Multi-Language Support: Add translation nodes for international guests
Guest Segmentation: VIP guests receive premium recovery offers
Timing Rules: Different workflows for during-stay vs post-checkout feedback
Review Platform Integration: Direct API connections to TripAdvisor, Google Reviews
Staff Training Alerts: Route feedback to specific department managers
Competitive Analysis: Track sentiment vs competitor properties
Seasonal Adjustments: Higher compensation during peak season to retain bookings
Loyalty Program Integration: Award points as part of service recovery
Follow-Up Sequences: Automated check-ins after issue resolution
Expected Results
60% reduction in negative online reviews - Proactive resolution before guests post publicly
85% faster service recovery - Automated workflows vs manual monitoring
40% increase in repeat bookings - Effective recovery turns complainers into loyalists
95% manager response rate - Instant alerts ensure nothing falls through cracks
3x increase in positive review requests - Automated outreach to satisfied guests
75% cost reduction in review management - Less time fighting bad reviews
90% guest satisfaction with recovery - Personalized, immediate responses
100% feedback tracking - Complete audit trail for quality improvement
50% improvement in staff training - Data-driven insights on recurring issues
30% reduction in compensation costs - AI optimizes offer value vs actual resolution
Use Cases
Luxury Resort (200 Rooms)
Scenario: Guest in oceanview suite complains about noisy pool area disrupting afternoon nap. Rating: 2/5. Feedback submitted at 2:47 PM during stay.
AI Analysis: Sentiment = negative (35/100), Urgency = high, Impact = high reputation risk. Key issue: noise disturbance. Category: amenities/environment.
Automated Response:
- 2:48 PM: Hotel manager receives urgent email and Slack alert
- 2:49 PM: AI generates recovery offer: complimentary room upgrade to quiet wing + $100 spa credit + late checkout
- 2:52 PM: Manager reviews AI recommendation, approves via phone
- 3:00 PM: Guest receives personalized apology email with upgrade offer
- 3:15 PM: Guest accepts, moves to premium suite
- 3:45 PM: Manager personally visits guest with welcome amenity
- Next Day: Guest updates internal feedback to 5/5
Result: $200 recovery cost prevents $5,000+ in future lost bookings from negative review. Guest becomes repeat customer, books 3 more stays over next year.
Business Hotel (80 Rooms)
Scenario: Corporate traveler rates stay 5/5, praises front desk staff professionalism and fast WiFi. Checkout feedback at 7:23 AM.
AI Analysis: Sentiment = positive (92/100), Urgency = low, Categories: staff excellence, amenities.
Automated Response:
- 7:24 AM: Thank you email sent with review request links (Google, TripAdvisor)
- Email includes 15% discount code for next stay (WELCOME-BACK-2025)
- Review links customized with pre-filled star ratings
- 11:30 AM: Guest posts 5-star Google review mentioning staff by name
Result: Positive review attracts 12 new corporate bookings over next quarter. Guest becomes regular weekly visitor. Zero manual effort required.
Budget Hotel Chain (150 Locations)
Scenario: Guest complains about unclean bathroom, slow check-in, and uncomfortable bed. Rating: 1/5. Multiple critical issues.
AI Analysis: Sentiment = negative (15/100), Urgency = CRITICAL, Impact = very high reputation risk. Categories: cleanliness, operations, room quality.
Automated Response:
- Instant email to hotel manager + regional director
- Slack alert to #operations-critical channel
- AI recommends: full refund + 2 free night voucher + immediate room change
- PMS ticket created for housekeeping inspection
- Guest receives apology within 10 minutes
- Manager calls guest personally within 20 minutes
- Room changed immediately, housekeeping staff retrained
Result: Guest accepts recovery offer, doesn't post negative review. Systemic cleaning issue identified and corrected across all 150 locations, preventing 1,000+ potential complaints.
Boutique B&B (12 Rooms)
Scenario: Couple celebrating anniversary rates stay 4/5, mentions minor issue with breakfast timing but overall lovely experience.
AI Analysis: Sentiment = positive (78/100), Urgency = low, Issue noted: breakfast service timing, Categories: dining, overall satisfaction.
Automated Response:
- Thank you email with review requests
- AI suggests small gesture: complimentary breakfast on next visit
- Owner receives gentle notification about breakfast timing feedback (not urgent)
- Follow-up email includes personalized anniversary wishes
Result: Couple posts glowing TripAdvisor review, becomes annual anniversary tradition. Breakfast timing adjusted based on feedback trend analysis. Personal touch strengthens brand loyalty.
Resort During Peak Season
Scenario: Family of 4 complains about overbooked pool area, long wait times at restaurant, stressed staff. Rating: 3/5. Peak season capacity issues.
AI Analysis: Sentiment = neutral-negative (45/100), Urgency = medium, Categories: capacity management, staffing, amenities access. AI notes this is systemic, not individual service failure.
Automated Response:
- Manager receives analysis highlighting capacity issues vs service quality
- AI recommends: restaurant priority reservations rest of stay + late checkout + $150 resort credit
- Recovery offer emphasizes "peak season challenges we're addressing"
- Guest receives empathetic communication acknowledging valid concerns
- Operations team receives alert about capacity strain for staffing adjustments
Result: Family accepts offer, enjoys remaining days. Operations team adds staff for following weekend. Feedback trends identify need for reservation system improvements, implemented before next season.
Pro Tips
QR Code Distribution: Place QR codes linking to feedback form in every room, at checkout desk, and in common areas
Timing Optimization: Send feedback requests on Day 2 of stay (not checkout) to enable real-time recovery
Manager Training: Educate managers on interpreting AI sentiment scores and urgency classifications
Recovery Budgets: Set department budgets for service recovery offers ($50-500 per incident)
Review Monitoring: Cross-reference internal feedback with online reviews to measure prevention effectiveness
Staff Recognition: Share positive feedback with staff members mentioned by name
Trend Analysis: Weekly reviews of Google Sheets data to identify recurring issues
Seasonal Patterns: Track sentiment scores across different seasons and events
Competitor Benchmarking: Compare your sentiment scores to industry averages
Follow-Up Surveys: Send 30-day post-stay surveys to guests who received service recovery
Loyalty Integration: Higher-tier loyalty members receive premium recovery offers
Language Customization: For international properties, adjust AI prompts for cultural norms
Response Time Tracking: Monitor average time from feedback to resolution
Cost-Benefit Analysis: Track recovery offer costs vs prevented negative review impact
Success Metrics Dashboard: Create Google Data Studio dashboard from Sheets data
Learning Resources
This workflow demonstrates advanced automation:
AI Agents with Multi-Dimensional Analysis: Sentiment scoring, urgency classification, impact assessment, and recovery recommendations
Conditional Logic Routing: Different workflows for positive, negative, and critical feedback
Real-Time Alerting: Multi-channel notifications (email + Slack) for urgent issues
Dynamic Content Generation: AI creates personalized emails based on sentiment analysis
API Integration Patterns: Property Management System ticket creation via HTTP requests
Data Aggregation: Complete feedback logging for business intelligence and reporting
Natural Language Processing: AI extracts key issues, categories, and emotional tone from free-text feedback
Decision Support Systems: Provides managers with AI recommendations and key considerations
Approval Workflows: Optional manager approval step for high-value recovery offers
Guest Communication Templates: Professional, empathetic email templates for all scenarios
Business Impact Metrics
Review Management ROI: Compare cost of service recovery vs reputation management services (typically $500-2000/month)
Guest Lifetime Value: Track repeat booking rates for guests who received service recovery vs those who didn't
Online Reputation Score: Monitor aggregate rating improvements on Google, TripAdvisor, Booking.com
Staff Efficiency: Calculate hours saved vs manual feedback monitoring and response
Revenue Protection: Estimate revenue preserved by preventing negative reviews (avg negative review costs hotel $2,000-5,000 in lost bookings)
Recovery Success Rate: Percentage of negative feedback resolved without resulting in online reviews
Response Time: Average minutes from feedback submission to initial response
First-Contact Resolution: Percentage of issues resolved without multiple interactions
Training ROI: Reduction in recurring issues after staff training based on feedback trends
Competitive Positioning: Sentiment score comparison vs competitor properties
Ready to Transform Your Guest Experience?
Import this template and turn guest feedback into your competitive advantage with AI-powered insights and automation! 🏨✨
Questions or customization? The workflow includes detailed sticky notes explaining each AI analysis component and decision logic.
Template Compatibility
- ✅ n8n version 1.0+
- ✅ Works with n8n Cloud and Self-Hosted
- ✅ No coding required for basic setup
- ✅ Fully customizable for advanced users
n8n Workflow: AI-Powered Hotel Guest Feedback Analysis and Service Recovery
This n8n workflow automates the process of collecting hotel guest feedback, performing sentiment analysis using GPT-4, and triggering service recovery actions based on negative sentiment. It helps hotels proactively address guest concerns and improve satisfaction.
What it does
This workflow performs the following steps:
- Listens for New Feedback: It is triggered whenever a new submission is received from a Jotform survey.
- Extracts Guest Feedback: It takes the raw guest feedback text from the Jotform submission.
- Performs Sentiment Analysis with GPT-4: It sends the guest feedback to an OpenAI Chat Model (GPT-4) to analyze the sentiment and identify key issues.
- Categorizes Sentiment: It uses an "If" node to categorize the sentiment as "Negative" or "Positive" based on the AI agent's output.
- Logs Feedback to Google Sheets: For all feedback, it appends the original feedback, the AI-generated sentiment, and any identified issues to a Google Sheet for record-keeping and further analysis.
- Triggers Service Recovery for Negative Feedback: If the sentiment is identified as "Negative", it sends an email notification via Gmail to a designated service recovery team or manager, including the guest's feedback and the AI-identified issues.
Prerequisites/Requirements
To use this workflow, you will need:
- n8n Account: An active n8n instance (cloud or self-hosted).
- Jotform Account: A Jotform account with a survey configured to collect guest feedback.
- OpenAI API Key: An OpenAI API key with access to GPT-4 or a compatible chat model.
- Google Sheets Account: A Google Sheets spreadsheet set up to log feedback.
- Gmail Account: A Gmail account configured for sending service recovery notifications.
Setup/Usage
- Import the Workflow:
- Download the provided JSON file for this workflow.
- In your n8n instance, click "Workflows" in the left sidebar.
- Click "New" and then "Import from JSON".
- Paste the JSON content or upload the file.
- Configure Credentials:
- Jotform Trigger: Set up your Jotform credential to connect to your Jotform account. Select the specific form you want to monitor.
- OpenAI Chat Model: Configure your OpenAI credential with your API key. Ensure the model is set to
gpt-4or a suitable alternative. - Google Sheets: Set up your Google Sheets credential. Specify the Spreadsheet ID and Sheet Name where the feedback should be logged.
- Gmail: Set up your Gmail credential.
- Customize Nodes:
- Jotform Trigger: Ensure the "Form" field is correctly linked to your guest feedback form.
- AI Agent: Review the prompt for the AI Agent to ensure it extracts the desired sentiment and issues from the feedback.
- Edit Fields (Set): This node prepares the data for Google Sheets. Adjust the fields if your Jotform submission or Google Sheet columns differ.
- If: Verify the conditions in the "If" node to correctly identify "Negative" sentiment based on the AI agent's output.
- Google Sheets: Map the input fields to the correct columns in your Google Sheet.
- Gmail: Customize the recipient email address, subject, and body of the service recovery email to include relevant guest feedback details.
- Activate the Workflow: Once all credentials and configurations are set, activate the workflow by toggling the "Active" switch in the top right corner of the workflow editor.
Now, whenever a new guest feedback is submitted via your Jotform, this workflow will automatically process it, analyze its sentiment, log it, and trigger a service recovery email for negative feedback.
Related Templates
Track competitor SEO keywords with Decodo + GPT-4.1-mini + Google Sheets
This workflow automates competitor keyword research using OpenAI LLM and Decodo for intelligent web scraping. Who this is for SEO specialists, content strategists, and growth marketers who want to automate keyword research and competitive intelligence. Marketing analysts managing multiple clients or websites who need consistent SEO tracking without manual data pulls. Agencies or automation engineers using Google Sheets as an SEO data dashboard for keyword monitoring and reporting. What problem this workflow solves Tracking competitor keywords manually is slow and inconsistent. Most SEO tools provide limited API access or lack contextual keyword analysis. This workflow solves that by: Automatically scraping any competitor’s webpage with Decodo. Using OpenAI GPT-4.1-mini to interpret keyword intent, density, and semantic focus. Storing structured keyword insights directly in Google Sheets for ongoing tracking and trend analysis. What this workflow does Trigger — Manually start the workflow or schedule it to run periodically. Input Setup — Define the website URL and target country (e.g., https://dev.to, france). Data Scraping (Decodo) — Fetch competitor web content and metadata. Keyword Analysis (OpenAI GPT-4.1-mini) Extract primary and secondary keywords. Identify focus topics and semantic entities. Generate a keyword density summary and SEO strength score. Recommend optimization and internal linking opportunities. Data Structuring — Clean and convert GPT output into JSON format. Data Storage (Google Sheets) — Append structured keyword data to a Google Sheet for long-term tracking. Setup Prerequisites If you are new to Decode, please signup on this link visit.decodo.com n8n account with workflow editor access Decodo API credentials OpenAI API key Google Sheets account connected via OAuth2 Make sure to install the Decodo Community node. Create a Google Sheet Add columns for: primarykeywords, seostrengthscore, keyworddensity_summary, etc. Share with your n8n Google account. Connect Credentials Add credentials for: Decodo API credentials - You need to register, login and obtain the Basic Authentication Token via Decodo Dashboard OpenAI API (for GPT-4o-mini) Google Sheets OAuth2 Configure Input Fields Edit the “Set Input Fields” node to set your target site and region. Run the Workflow Click Execute Workflow in n8n. View structured results in your connected Google Sheet. How to customize this workflow Track Multiple Competitors → Use a Google Sheet or CSV list of URLs; loop through them using the Split In Batches node. Add Language Detection → Add a Gemini or GPT node before keyword analysis to detect content language and adjust prompts. Enhance the SEO Report → Expand the GPT prompt to include backlink insights, metadata optimization, or readability checks. Integrate Visualization → Connect your Google Sheet to Looker Studio for SEO performance dashboards. Schedule Auto-Runs → Use the Cron Node to run weekly or monthly for competitor keyword refreshes. Summary This workflow automates competitor keyword research using: Decodo for intelligent web scraping OpenAI GPT-4.1-mini for keyword and SEO analysis Google Sheets for live tracking and reporting It’s a complete AI-powered SEO intelligence pipeline ideal for teams that want actionable insights on keyword gaps, optimization opportunities, and content focus trends, without relying on expensive SEO SaaS tools.
By Ranjan DailataGenerate song lyrics and music from text prompts using OpenAI and Fal.ai Minimax
Spark your creativity instantly in any chat—turn a simple prompt like "heartbreak ballad" into original, full-length lyrics and a professional AI-generated music track, all without leaving your conversation. 📋 What This Template Does This chat-triggered workflow harnesses AI to generate detailed, genre-matched song lyrics (at least 600 characters) from user messages, then queues them for music synthesis via Fal.ai's minimax-music model. It polls asynchronously until the track is ready, delivering lyrics and audio URL back in chat. Crafts original, structured lyrics with verses, choruses, and bridges using OpenAI Submits to Fal.ai for melody, instrumentation, and vocals aligned to the style Handles long-running generations with smart looping and status checks Returns complete song package (lyrics + audio link) for seamless sharing 🔧 Prerequisites n8n account (self-hosted or cloud with chat integration enabled) OpenAI account with API access for GPT models Fal.ai account for AI music generation 🔑 Required Credentials OpenAI API Setup Go to platform.openai.com → API keys (sidebar) Click "Create new secret key" → Name it (e.g., "n8n Songwriter") Copy the key and add to n8n as "OpenAI API" credential type Test by sending a simple chat completion request Fal.ai HTTP Header Auth Setup Sign up at fal.ai → Dashboard → API Keys Generate a new API key → Copy it In n8n, create "HTTP Header Auth" credential: Name="Fal.ai", Header Name="Authorization", Header Value="Key [Your API Key]" Test with a simple GET to their queue endpoint (e.g., /status) ⚙️ Configuration Steps Import the workflow JSON into your n8n instance Assign OpenAI API credentials to the "OpenAI Chat Model" node Assign Fal.ai HTTP Header Auth to the "Generate Music Track", "Check Generation Status", and "Fetch Final Result" nodes Activate the workflow—chat trigger will appear in your n8n chat interface Test by messaging: "Create an upbeat pop song about road trips" 🎯 Use Cases Content Creators: YouTubers generating custom jingles for videos on the fly, streamlining production from idea to audio export Educators: Music teachers using chat prompts to create era-specific folk tunes for classroom discussions, fostering interactive learning Gift Personalization: Friends crafting anniversary R&B tracks from shared memories via quick chats, delivering emotional audio surprises Artist Brainstorming: Songwriters prototyping hip-hop beats in real-time during sessions, accelerating collaboration and iteration ⚠️ Troubleshooting Invalid JSON from AI Agent: Ensure the system prompt stresses valid JSON; test the agent standalone with a sample query Music Generation Fails (401/403): Verify Fal.ai API key has minimax-music access; check usage quotas in dashboard Status Polling Loops Indefinitely: Bump wait time to 45-60s for complex tracks; inspect fal.ai queue logs for bottlenecks Lyrics Under 600 Characters: Tweak agent prompt to enforce fuller structures like [V1][C][V2][B][C]; verify output length in executions
By Daniel NkenchoAutomate invoice processing with OCR, GPT-4 & Salesforce opportunity creation
PDF Invoice Extractor (AI) End-to-end pipeline: Watch Drive ➜ Download PDF ➜ OCR text ➜ AI normalize to JSON ➜ Upsert Buyer (Account) ➜ Create Opportunity ➜ Map Products ➜ Create OLI via Composite API ➜ Archive to OneDrive. --- Node by node (what it does & key setup) 1) Google Drive Trigger Purpose: Fire when a new file appears in a specific Google Drive folder. Key settings: Event: fileCreated Folder ID: google drive folder id Polling: everyMinute Creds: googleDriveOAuth2Api Output: Metadata { id, name, ... } for the new file. --- 2) Download File From Google Purpose: Get the file binary for processing and archiving. Key settings: Operation: download File ID: ={{ $json.id }} Creds: googleDriveOAuth2Api Output: Binary (default key: data) and original metadata. --- 3) Extract from File Purpose: Extract text from PDF (OCR as needed) for AI parsing. Key settings: Operation: pdf OCR: enable for scanned PDFs (in options) Output: JSON with OCR text at {{ $json.text }}. --- 4) Message a model (AI JSON Extractor) Purpose: Convert OCR text into strict normalized JSON array (invoice schema). Key settings: Node: @n8n/n8n-nodes-langchain.openAi Model: gpt-4.1 (or gpt-4.1-mini) Message role: system (the strict prompt; references {{ $json.text }}) jsonOutput: true Creds: openAiApi Output (per item): $.message.content → the parsed JSON (ensure it’s an array). --- 5) Create or update an account (Salesforce) Purpose: Upsert Buyer as Account using an external ID. Key settings: Resource: account Operation: upsert External Id Field: taxid_c External Id Value: ={{ $json.message.content.buyer.tax_id }} Name: ={{ $json.message.content.buyer.name }} Creds: salesforceOAuth2Api Output: Account record (captures Id) for downstream Opportunity. --- 6) Create an opportunity (Salesforce) Purpose: Create Opportunity linked to the Buyer (Account). Key settings: Resource: opportunity Name: ={{ $('Message a model').item.json.message.content.invoice.code }} Close Date: ={{ $('Message a model').item.json.message.content.invoice.issue_date }} Stage: Closed Won Amount: ={{ $('Message a model').item.json.message.content.summary.grand_total }} AccountId: ={{ $json.id }} (from Upsert Account output) Creds: salesforceOAuth2Api Output: Opportunity Id for OLI creation. --- 7) Build SOQL (Code / JS) Purpose: Collect unique product codes from AI JSON and build a SOQL query for PricebookEntry by Pricebook2Id. Key settings: pricebook2Id (hardcoded in script): e.g., 01sxxxxxxxxxxxxxxx Source lines: $('Message a model').first().json.message.content.products Output: { soql, codes } --- 8) Query PricebookEntries (Salesforce) Purpose: Fetch PricebookEntry.Id for each Product2.ProductCode. Key settings: Resource: search Query: ={{ $json.soql }} Creds: salesforceOAuth2Api Output: Items with Id, Product2.ProductCode (used for mapping). --- 9) Code in JavaScript (Build OLI payloads) Purpose: Join lines with PBE results and Opportunity Id ➜ build OpportunityLineItem payloads. Inputs: OpportunityId: ={{ $('Create an opportunity').first().json.id }} Lines: ={{ $('Message a model').first().json.message.content.products }} PBE rows: from previous node items Output: { body: { allOrNone:false, records:[{ OpportunityLineItem... }] } } Notes: Converts discount_total ➜ per-unit if needed (currently commented for standard pricing). Throws on missing PBE mapping or empty lines. --- 10) Create Opportunity Line Items (HTTP Request) Purpose: Bulk create OLIs via Salesforce Composite API. Key settings: Method: POST URL: https://<your-instance>.my.salesforce.com/services/data/v65.0/composite/sobjects Auth: salesforceOAuth2Api (predefined credential) Body (JSON): ={{ $json.body }} Output: Composite API results (per-record statuses). --- 11) Update File to One Drive Purpose: Archive the original PDF in OneDrive. Key settings: Operation: upload File Name: ={{ $json.name }} Parent Folder ID: onedrive folder id Binary Data: true (from the Download node) Creds: microsoftOneDriveOAuth2Api Output: Uploaded file metadata. --- Data flow (wiring) Google Drive Trigger → Download File From Google Download File From Google → Extract from File → Update File to One Drive Extract from File → Message a model Message a model → Create or update an account Create or update an account → Create an opportunity Create an opportunity → Build SOQL Build SOQL → Query PricebookEntries Query PricebookEntries → Code in JavaScript Code in JavaScript → Create Opportunity Line Items --- Quick setup checklist 🔐 Credentials: Connect Google Drive, OneDrive, Salesforce, OpenAI. 📂 IDs: Drive Folder ID (watch) OneDrive Parent Folder ID (archive) Salesforce Pricebook2Id (in the JS SOQL builder) 🧠 AI Prompt: Use the strict system prompt; jsonOutput = true. 🧾 Field mappings: Buyer tax id/name → Account upsert fields Invoice code/date/amount → Opportunity fields Product name must equal your Product2.ProductCode in SF. ✅ Test: Drop a sample PDF → verify: AI returns array JSON only Account/Opportunity created OLI records created PDF archived to OneDrive --- Notes & best practices If PDFs are scans, enable OCR in Extract from File. If AI returns non-JSON, keep “Return only a JSON array” as the last line of the prompt and keep jsonOutput enabled. Consider adding validation on parsing.warnings to gate Salesforce writes. For discounts/taxes in OLI: Standard OLI fields don’t support per-line discount amounts directly; model them in UnitPrice or custom fields. Replace the Composite API URL with your org’s domain or use the Salesforce node’s Bulk Upsert for simplicity.
By Le Nguyen