Example Google Sheets Integration

Google Apps Script integration to automatically log incoming SMS messages to Google Sheets

About The Project · Getting Started · Usage · Contributing

• 🌐 About The Project
  • 🛠️ Built With
• 🚀 Getting Started
  • 📋 Prerequisites
  • ⚙️ Installation
• 💻 Usage
  • 📨 Webhook Payload Structure
  • 🔧 Troubleshooting
• 🤝 Contributing
• 📜 License

🌐 About The Project

This project provides a Google Apps Script integration for SMSGate that automatically logs incoming SMS messages to a Google Sheet. When SMS messages are received through the app, a webhook is sent to this script which parses the message data and appends it to your Google Sheet with the following columns:

• Timestamp: When the message was received
• From: The phone number that sent the message
• Message: The SMS message content
• DeviceID: The device ID
• MessageID: The unique message ID

🛠️ Built With

• Google Apps Script
• Google Sheets

back to top

🚀 Getting Started

Follow these instructions to set up the SMSGate Google Sheets integration.

📋 Prerequisites

• A Google account
• Access to Google Sheets
• An SMSGate app on your phone

⚙️ Installation

1. Create a new Google Sheet

   • Go to sheets.google.com

   • Create a new blank spreadsheet

   • Name the spreadsheet "SMS Messages" or your preferred name

   • Rename the first sheet to "Messages" (this is required by the script)

   • Add the following headers to row 1:

     Timestamp | From | Message | DeviceID | MessageID
     

2. Open the Apps Script Editor

   • In your Google Sheet, click on Extensions > Apps Script
   • This will open the Apps Script editor in a new tab

3. Deploy the Script

   • Remove any existing code in the editor
   • Copy the code from code.gs and paste it into the Apps Script editor
   • Click the "Save project" icon (floppy disk)

4. Deploy the Web App

   • Click on "Deploy" > "New deployment"

   • Click on the "Select type" dropdown and choose "Web app"

   • Under "Execute as", select "Me (your account)"

   • Under "Who has access", select "Anyone"

   • Click "Deploy"

   • Authorize the script when prompted (you may need to select your Google account)

   • Copy the "Web app URL", e.g.

     https://script.google.com/macros/s/AKfycbx.../exec
     

5. Configure sms-gate.app Webhook

   • Register a new webhook with the URL from step 4

6. Test the Setup

   • Send a test SMS message to your phone
   • Check your Google Sheet - the message should appear automatically
   • Verify all columns are populated correctly

back to top

💻 Usage

📨 Webhook Payload Structure

The script expects webhook payloads from SMSGate in the following format:

{
  "deviceId": "your-device-id",
  "payload": {
    "messageId": "unique-message-id",
    "message": "Hello, this is a test message",
    "phoneNumber": "+1234567890",
    "receivedAt": "2024-12-01T10:30:00Z"
  }
}

🔧 Troubleshooting

• Sheet not found: Ensure your sheet is named exactly "Messages"
• Data not appearing: Check that your webhook URL is correct and accessible
• Authorization errors: Re-deploy the web app with proper permissions
• Column mismatch: Verify your headers match exactly: "Timestamp", "From", "Message", "DeviceID", "MessageID"
• Duplicate messages in sheet: If you notice the same SMS message being logged multiple times, this occurs because Google Apps Script returns a 302 redirect for POST requests. SMSGate retries webhook delivery on non-2xx responses. To fix:
  • Option 1: Set "Retry count" to 1 in SMSGate settings (Settings → Webhooks → Retry count)
  • Option 2: Implement deduplication in your Apps Script using the unique messageId from the webhook payload

back to top

🤝 Contributing

Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.

If you have a suggestion that would make this better, please fork the repo and create a pull request. You can also simply open an issue with the tag "enhancement". Don't forget to give the project a star! Thanks again!

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/AmazingFeature)
3. Commit your Changes (git commit -m 'Add some AmazingFeature')
4. Push to the Branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

back to top

📜 License

Distributed under the Apache License 2.0. See LICENSE for more information.

back to top