Notes on using Google Apps Script to insert into a Google Sheet from a Retell AI tool call

This is an overview of a very quick and easy method to implement a tool/function call in RetellAI that will allow you to insert data into a Google Sheet, without requiring any automation tools (e.g. Make or n8n) or web/app hosting. If there is enough demand for this, I may make a video tutorial, but as of this date (March 8, 2025), the following works correctly.

Here is the process:

Create a Google Sheet to store your data
- Create a header row (and optionally freeze the row). The script we are building will use this to properly map data when inserting sheet rows. For example: First Name, Last Name, Opinion
- In the Sheets menu, click "Extensions," and choose "Apps Script"
- By default, you will see a blank function (myFunction). Delete this function.
- Insert the following function in its place:
Apps Script
```
function doPost(e) {
    try {
        // Parse the incoming POST data as JSON.
        var payload = JSON.parse(e.postData.contents);
        var rowData = payload.args; // e.g. { first_name: "Alice", email: "alice@example.com" }

        // Open your Google Sheet.
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

        // Retrieve the header row.
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

        // Build a new row by converting each header into snake_case.
        // For instance, "First Name" becomes "first_name".
        var row = headers.map(function(header) {
        // Normalize: trim, convert to lowercase, and replace spaces with underscores.
        var normalizedHeader = header.toLowerCase().trim().replace(/\s+/g, "_");
        // Return the matching value from rowData or an empty string if not provided.
        return rowData[normalizedHeader] || "";
        });

        // Append the new row to the sheet.
        sheet.appendRow(row);

        return ContentService
        .createTextOutput(JSON.stringify({ result: "success" }))
        .setMimeType(ContentService.MimeType.JSON);

    } catch (err) {
        return ContentService
        .createTextOutput(JSON.stringify({ result: "error", error: err.toString() }))
        .setMimeType(ContentService.MimeType.JSON);
    }
}
```
- If you have changed the name of the sheet (tab) you will need to modify the var sheet = line.
- Next, you should give your project a name. Click "Untitled Project" and name it accordingly.
- Next, press the Deploy button. Choose "New Deployment"
- You will be prompted to choose a deployment type. Click the gear icon and and choose "Web App"
- Give your web app a description (e.g. Retell Tool Call Endpoint). Leave the Execute As setting as "Me (youremail@gmail.com)". Important: You must set "Who has access" to "Anyone."
- Click "Deploy." This will take several seconds. You will then be prompted to Authorize Access.
- Click "Authorize Access."
- Choose your Google account. You will receive a warning that Google hasn't verified this app. The “unverified app” warning appears because Google hasn’t reviewed your script for certain sensitive scopes or because it’s a new project. Even though it’s not verified, the app is still running on Google’s secure infrastructure and only uses the permissions you’ve granted. As you're the developer, and know exactly what your script is doing, the risk is minimal. You can also add additional security checks if you like (like secret tokens) to ensure only legitimate requests are processed.
- Disregard the warning, and click "Advanced." This will cause a message to appear: "Continue only if you understand the risks and trust the developer." As you are the developer, hopefully you can trust yourself.
- Click on the link below this (e.g. "Go to [Your Project Name] (unsafe)")
- You will now see a screen asking you to authorize the project to access your Google Account. Click "Allow."
- You will be provided with a Deployment ID and Web App URL. Copy the Web App URL and keep it in a safe place as you will need this shortly. The URL will look something like https://script.google.com/macros/s/the_deployment_id_as_a_very_long_string_here/exec

Now you are ready to create your tool/function call in Retell.

Create a custom tool to send data from Retell to your Google Sheet

Load your agent in the Retell dashboard. For this example, I will assume you are using a single prompt agent.

Build your agent script. For example:

Example Retell Script

# ROLE

You are an agent who is helping me test a Google AppScript integration.

# TASK

1.) Greet the caller and explain your purpose.
2.) Collect their first name.  Verify you have it spelled correctly.  Iterate until you are sure it is correct.
3.) Collect their last name.  Verify you have it spelled correctly.  Iterate until you are sure it is correct.
4.) Ask their opinion on something.
5.) Invoke tool/function "send_to_sheet"
6.) End the call

Create a new Custom Function. Call it something like "send_to_sheet."
Give it a description. For example, "Send the first name, last name and opinion of the user to the Google sheet."
"Your URL" will be the Web App URL you saved before (e.g. https://script.google.com/...../exec)

Set up your JSON schema something like this:

Tool Parameters

{
    "type": "object",
    "properties": {
        "first_name": {
            "type": "string",
            "description": "The first name of the user."
        },
        "last_name": {
            "type": "string",
            "description": "The last name of the user."
        },
        "opinion": {
            "type": "string",
            "description": "The opinion of the user."
        }
    },
    "required": [
        "first_name",
        "last_name",
        "opinion"
    ]
}

Important Note: The Apps Script script above will correctly map keys like "first_name" to sheet header items like "First Name"

Save the tool call
Test the agent and ensure rows are inserted!

Note About Usage Limits

Google does place usage limits on Apps Script, which may be relevant if you are using this in a high volume use case. That said, average execution time is around 1.5 seconds or less for this script. I believe that free (consumer) Google accounts are limited to 90 minutes of runtime per day (as of March 8, 2025), meaning a given account should have a few thousand runs available per day (assuming no other Apps Script usage on the account). Limits are generally higher on Workspace acounts.