How to Send Automation Data to Google Sheets – Vista Social

This guide walks you through setting up a Google Sheet to automatically receive data from Vista Social automations. When your automation completes data collection, the results will be sent directly to your spreadsheet in real-time.

If you are looking for more destination options where your automation results can be delivered, please review our Data Collection with DM Automation guide.

Overview

Vista Social automations can send collected data to any REST API endpoint. By creating a simple Google Apps Script, you can turn any Google Sheet into a data destination that automatically organizes incoming data into rows and columns.

What you'll accomplish:

Create a Google Sheet to store your automation data
Set up a Google Apps Script to receive data
Connect it to Vista Social as a data collection destination

Time required: 10-15 minutes

Skill level: Beginner ( no coding experience required

Step 1: Create Your Google Sheet

Go to Google Sheets and create a new spreadsheet
Give your spreadsheet a descriptive name (e.g., "Vista Social Lead Data" or "Automation Results")
Leave the sheet empty ( the script will automatically create column headers based on the data you send

Step 2: Open the Apps Script Editor

In your Google Sheet, click Extensions in the top menu
Select Apps Script A new browser tab will open with the Apps Script editor
Delete any existing code in the editor

Step 3: Add the Script Code

Copy and paste the following code into the Apps Script editor:

function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = JSON.parse(e.postData.contents);
  
  // Flatten nested objects to dot notation
  var flatData = flattenObject(data);
  
  var lastCol = sheet.getLastColumn();
  var headers = [];
  
  // Only try to read headers if there are columns
  if (lastCol > 0) {
    headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  }
  
  // If sheet is empty, create headers from the incoming data
  if (headers.filter(String).length === 0) {
    headers = Object.keys(flatData);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }
  
  // Build row based on existing headers
  var row = headers.map(function(header) {
    return flatData[header] !== undefined ? flatData[header] : '';
  });
  
  // Check for new fields not in headers yet
  Object.keys(flatData).forEach(function(key) {
    if (headers.indexOf(key) === -1) {
      headers.push(key);
      row.push(flatData[key]);
      // Add new header
      sheet.getRange(1, headers.length).setValue(key);
    }
  });
  
  sheet.appendRow(row);
  
  return ContentService.createTextOutput(JSON.stringify({status: 'success', fields: Object.keys(flatData)}))
    .setMimeType(ContentService.MimeType.JSON);
}

function flattenObject(obj, prefix) {
  prefix = prefix || '';
  var result = {};
  
  Object.keys(obj).forEach(function(key) {
    var fullKey = prefix ? prefix + '.' + key : key;
    
    if (typeof obj[key] === 'object' && obj[key] !== null && !Array.isArray(obj[key])) {
      // Recursively flatten nested objects
      var nested = flattenObject(obj[key], fullKey);
      Object.keys(nested).forEach(function(nestedKey) {
        result[nestedKey] = nested[nestedKey];
      });
    } else if (Array.isArray(obj[key])) {
      // Convert arrays to JSON string
      result[fullKey] = JSON.stringify(obj[key]);
    } else {
      result[fullKey] = obj[key];
    }
  });
  
  return result;
}

Step 4: Deploy as a Web App

Click the Deploy button in the top right corner
Select New deployment
Click the gear icon next to "Select type" and choose Web app
Configure the deployment settings:
1. Description: Enter something like "Vista Social Data Receiver"
2. Execute as: Select Me
3. Who has access: Select Anyone
Click Deploy
If prompted, click Authorize access and follow the prompts to grant permissions
Copy the Web app URL. You'll need this for Vista Social

Important: The URL will look something like:
https://script.google.com/macros/s/AKfycb.../exec

Step 5: Connect to Vista Social

In Vista Social, navigate to your automation settings
Configure your data collection destination
Select REST API as the destination type. Paste the Google Apps Script URL you copied in Step 4
Save your automation

How It Works

When your Vista Social automation runs and collects data, it sends that data to your Google Apps Script URL. The script then:

Creates headers automatically. The script will automatically create column headers based on the data you send Step 2: Open the Apps Script Editor In your Google Sheet, click Extensions in the top menu Select Apps Script A new browser tab will open with the Apps Script editor Delete any existing code in the editor Step 3: Add the Script CodeCopy and paste the following code into the Apps Script editor: function doPost(e) { var sheet = SpreadsheetApp. The first time data arrives, the script creates column headers based on the field names in your data
Handles nested data — If your data has nested structures (like user.name or contact.email), the script flattens them using dot notation for clear column headers
Adds new columns dynamically. The script will automatically create column headers based on the data you send Step 2: Open the Apps Script Editor In your Google Sheet, click Extensions in the top menu Select Apps Script A new browser tab will open with the Apps Script editor Delete any existing code in the editor Step 3: Add the Script CodeCopy and paste the following code into the Apps Script editor: function doPost(e) { var sheet = SpreadsheetApp. If future data includes new fields, the script automatically adds new columns
Appends each record as a new row — Every automation run adds a new row to your sheet

Updating Your Script

Updating the script is not necessary, but if you wish to update, you can certainly do so and customize its behavior even further!

Need further help?

If you have any questions or need additional help, feel free to contact our awesome support team. We are here to assist you! 💙

Related Reading:

Data Collection with DM Automations