No & Low Code Solutions

Copy/paste options for low-code Activity API Use

In this section

Need help? Email us at support@villagelabs.co.

  1. Using Google Sheets and an Apps Script

  2. Using an Airtable automation

  3. Using Hubspot Workflows

Background

When you integrate with the Village API, Village listens to user activities like actions or transactions on your platform that are associated with Triggers you've created. When these activities occur, rules are then triggered and Village takes action according to relevant rules logic.

By contrast, this page explains how to pass user activities to Village without an API connection using low-code or no-code solutions like Google Sheets and a Google Apps Script, Airtable automations, and Hubspot Workflows. This will allow you to send relevant user activities (eg. events, actions, or transactions) to Village and effectively replicate the function of the API. These user activities are then able to trigger rules and cause output operations to occur in Village.

Using Google Sheets

Please read: When you are connecting to Village through our Google Sheets Solution, you are interfacing directly with Village's APIs when you run the scripts in Sheets. It is therefore extremely important that you read the relevant API documentation for each script below. This will tell you the correct format to input data (for example, the Associated Users input is case sensitive) and what each command does. In parallel, you should also read the instructions below for how to operate the sheets, and anything you should be aware of.

This will allow you to populate a sheet with relevant user activities (eg. events, actions, or transactions), and then run a script to pass them through to Village (effectively replicating the function of the API). These user activities are then able to trigger rules and cause output operations to occur in Village.

Go to our Google Sheet Example and File -> Make a Copy. Save this copy in your company drive.

Warning: If you use this method, we highly recommend limiting access to this spreadsheet to ensure your API key remains private.

Script Setup

In the Settings tab, paste your API Key to replace "YOUR_API_KEY" in B3. Do the same with "YOUR_NETWORK_ID". You can find both items by navigating to Village's Side Menu -> API Keys.

Go back to the Google Sheet. Navigate on the menu bar to Google Sheets -> Extensions -> Apps Scripts.

When you make a copy of the spreadsheet, the Apps Script should also copy and appear when you open Apps Script.

If the script has not copied across and you see an empty editor, expand this section and follow the steps here.

  1. Delete the existing placeholder function and replace it with the code below.

  2. Once you have done this, do a hard reload of the spreadsheet (i.e. click the reload button in the top left of your browser) and you should now see a new dropdown menu option: Custom Menu -> Run Script.

```javascript
// This function is automatically run when the Google Sheets is loaded
function onOpen() {
  // Get user interface
  const ui = SpreadsheetApp.getUi();

  // Create a new menu with sub-menu options
  ui.createMenu('Spreadsheet API Magic')
    .addItem('Run Activity Script', 'submitActivityData')
    .addItem('Run Segments Script', 'submitSegmentData')
    .addItem('Run User Status Script', 'submitUserStatusData')
    .addItem('Run Referral Script', 'submitAddConnectionData')
    .addToUi();  // Add this menu to user interface
}

//ACTIVITIES *********** 

// Function to submit activity data to API

function submitActivityData() {
  // Get the active spreadsheet and specific sheets within it
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = spreadsheet.getSheetByName("Settings");
  const activitiesSheet = spreadsheet.getSheetByName("Activities");

  // Get values from the "Settings" sheet
  const apiKey = settingsSheet.getRange("B2").getValue();
  const networkId = settingsSheet.getRange("B3").getValue();
  const activityUrl = settingsSheet.getRange("B4").getValue();
  const path = settingsSheet.getRange("B5").getValue();

  // Construct the API endpoint URL
  const apiEndpoint = `${activityUrl}/networks/${networkId}/${path}`;

  // Get data from the "Activities" sheet
  const activitiesData = activitiesSheet.getDataRange().getValues();

  let requests = [];
  // Loop over each row in activitiesData, starting from the second row
  for (let i = 1; i < activitiesData.length; i++) {
    let row = activitiesData[i];

    // Skip this row if previously submitted successfully
    if (row[0] === 'Submitted' && row[1] === 'Success') continue;

    // Create users object if the respective cells are not empty
    let users = {};
    if (row[4] && row[5]) users[row[4]] = row[5];
    if (row[6] && row[7]) users[row[6]] = row[7];

    // Prepare payload for API request
    let payload = {
      "activity_short_id": row[2],
      "amount": String(row[3]),
      "users": users,
      "metadata": {
        "activity_timestamp": row[8] ?? null
      }
    };

    // Set options for API request
    let options = {
      "method": "POST",
      "headers": {
        "Authorization": `Bearer ${apiKey}`,
        "Content-Type": "application/json"
      },
      "payload": JSON.stringify(payload),
      "muteHttpExceptions": true  // Prevent exceptions from halting the execution
    };

    requests.push({
      index: i + 0,
      url: apiEndpoint,
      ...options,
    });

    // Make the API request and handle the response
    // Once we hit 50 requests or we have no requests left let's process them all at once
    if (requests.length >= 20 || i === activitiesData.length - 1) {
      try {
        // Send group of 50 requests using fetchAll
        let responses = UrlFetchApp.fetchAll(requests);
        // Go through each of the resposnes and process them
        responses.forEach((r, i) => {
          const responseCode = r.getResponseCode();
          if (responseCode === 200) {
            activitiesSheet.getRange(requests[i].index + 1, 1, 1, 2).setValues([["Submitted", "Success"]]);
          } else {
            activitiesSheet.getRange(requests[i].index + 1, 1, 1, 2).setValues([["Submitted", `Failure: ${responseCode}`]]);
          }
        });

      } catch (e) {
        requests.map((r) => {
          activitiesSheet.getRange(r.index + 1, 1, 1, 2).setValues([["Submitted", `Failure: ${e.message}`]]);
        })
      } finally {
        // Always clear requests for next round of grouped messages
        requests = [];
      }
    }
  }
}

// SEGMENTS *********** 

// Function to submit segment data to API
function submitSegmentData() {
  // Get the active spreadsheet and specific sheets within it
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = ss.getSheetByName("Settings");
  const segmentsSheet = ss.getSheetByName("Segments");

  // Get values from the "Settings" sheet
  const apiKey = settingsSheet.getRange("B2").getValue();
  const networkId = settingsSheet.getRange("B3").getValue();
  const endpointUrl = settingsSheet.getRange("B4").getValue();
  const path = settingsSheet.getRange("B6").getValue();

  // Construct the API endpoint URL
  const apiEndpoint = `${endpointUrl}/networks/${networkId}/${path}`;

  // Get data from the "Segments" sheet
  const segmentsData = segmentsSheet.getDataRange().getValues();

  // Loop over each row in segmentsData, starting from the second row
  for (let i = 1; i < segmentsData.length; i++) {
    let row = segmentsData[i];

    // Skip row if previously submitted successfully
    if (row[0] === 'Success') continue;

    // Prepare data object for API request
    let postData = {
      "user": row[2],
      "segment_id": parseInt(row[3], 10),  // Convert to integer
      "user_status": row[4],
      "metadata": {
        "reference_id": row[5],
        "status_change_timestamp": parseInt(row[6], 10),  // Convert to integer
        "description": row[7]
        // Add more fields if necessary
      }
    };

    // Set options for API request
    let options = {
      "method": "POST",
      "headers": {
        "Authorization": `Bearer ${apiKey}`,
        "Content-Type": "application/json"
      },
      "payload": JSON.stringify(postData),
      "muteHttpExceptions": true  // Prevent exceptions from halting the execution
    };

    // Make the API call and handle response
    try {
      let response = UrlFetchApp.fetch(apiEndpoint, options);
      let responseCode = response.getResponseCode();
      let responseContentText = response.getContentText();

      if (responseCode >= 200 && responseCode < 300) {
        segmentsSheet.getRange(i + 1, 1, 1, 2).setValues([["Success", responseCode]]);
      } else {
        segmentsSheet.getRange(i + 1, 1, 1, 2).setValues([["Failure", responseCode]]);
        segmentsSheet.getRange(i + 1, 5).setValue(responseContentText);  // Log the full response in column E
      }
    } catch (e) {
      segmentsSheet.getRange(i + 1, 1, 1, 2).setValues([["Failure: Exception", ""]]);
      segmentsSheet.getRange(i + 1, 5).setValue(e.toString());  // Log the full exception in column E
    }
  }
}


// USER STATUS **********************
function submitUserStatusData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = ss.getSheetByName("Settings");
  const userStatusSheet = ss.getSheetByName("User Status");

  const apiKey = settingsSheet.getRange("B2").getValue();
  const networkId = settingsSheet.getRange("B3").getValue();
  const endpointUrl = settingsSheet.getRange("B4").getValue();
  const path = settingsSheet.getRange("B7").getValue();

  const url = `${endpointUrl}/networks/${networkId}/${path}`;
  const userStatusData = userStatusSheet.getDataRange().getValues();

  for (let i = 1; i < userStatusData.length; i++) {
    let row = userStatusData[i];

    if (row[0] === 'Success') continue;

    let postData = {
      "user": row[2],
      "status_change": row[3],
      "referrer": row[4],
      "segment_adds": [row[5]]
    };

    let options = {
      "method": "POST",
      "headers": {
        "Authorization": `Bearer ${apiKey}`,
        "Content-Type": "application/json"
      },
      "payload": JSON.stringify(postData),
      "muteHttpExceptions": true
    };

    try {
      let response = UrlFetchApp.fetch(url, options);
      let responseCode = response.getResponseCode();

      if (responseCode >= 200 && responseCode < 300) {
        userStatusSheet.getRange(i + 1, 1, 1, 2).setValues([["Success", responseCode]]);
      } else {
        let responseContent = JSON.parse(response.getContentText());
        let errorMessage = responseContent.error || "Unknown error";
        userStatusSheet.getRange(i + 1, 1, 1, 2).setValues([[`Failure: ${errorMessage}`, responseCode]]);
      }
    } catch (e) {
      userStatusSheet.getRange(i + 1, 1, 1, 2).setValues([[`Failure: ${e.message}`, ""]]);
    }
  }
}



// CONNECTIONS **********************
function submitAddConnectionData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = ss.getSheetByName("Settings");
  const referralSheet = ss.getSheetByName("Add Referral");

  const apiKey = settingsSheet.getRange("B2").getValue();
  const networkId = settingsSheet.getRange("B3").getValue();
  const endpointUrl = settingsSheet.getRange("B4").getValue();
  const path = settingsSheet.getRange("B8").getValue();
  const refProgramId = referralSheet.getRange("F2").getValue();

  const url = `${endpointUrl}/networks/${networkId}/${path}/${refProgramId}`;
  const referralData = referralSheet.getDataRange().getValues();

  for (let i = 1; i < referralData.length; i++) {
    let row = referralData[i];

    if (row[0] === 'Success') continue;

    let postData = {
      "user": row[2], //the new user being invited
      "connection": row[3], // the referrer
    };

    let options = {
      "method": "POST",
      "headers": {
        "Authorization": `Bearer ${apiKey}`,
        "Content-Type": "application/json"
      },
      "payload": JSON.stringify(postData),
      "muteHttpExceptions": true
    };

    try {
      let response = UrlFetchApp.fetch(url, options);
      let responseCode = response.getResponseCode();

      if (responseCode >= 200 && responseCode < 300) {
        referralSheet.getRange(i + 1, 1, 1, 2).setValues([["Success", responseCode]]);
      } else {
        let responseContent = JSON.parse(response.getContentText());
        let errorMessage = responseContent.error || "Unknown error";
        referralSheet.getRange(i + 1, 1, 1, 2).setValues([[`Failure: ${errorMessage}`, responseCode]]);
      }
    } catch (e) {
      referralSheet.getRange(i + 1, 1, 1, 2).setValues([[`Failure: ${e.message}`, ""]]);
    }
  }
}
```

Important: You may be asked to provide read/write access to this script by Google because this script needs those permissions to run. Ensure you're logged in to the proper Google account before approving permissions.

Run a Test

Pay attention to the amount field: most of the time, the amount field is going to be set to 1, since 1 event or activity of that type occurred. If you have a rule in Village that pays $50 when a task is completed, the rule will multiply the task activity amount (1) by $50 to calculate the payout. In this case, if you entered 50 in the activity amount field, this would result in a payout of 50 * $50 = $2500!

The exception to this rule is when you are configuring sales triggers. In this case, the amount field should be the total amount of the transaction or sale, eg. 500 if it was a $500 sale. For example, if a rule pays 1% cash back on each sale, it would multiply the rule amount field (.01) by the sales activity amount field ($500) to get a cash back of $5 for that sale.

We recommend running tests BEFORE trying in production. To run a test, set up a test program similar to what's in the Admin Quickstart ->.

Next, go to the "Inputs" tab. You can input some number of activities as tests. Make sure you use the correct Short ID for the Activities submitted (find in the Village Side Menu -> Triggers page) and the user_1_type matches a valid User type (which can be found on the same Trigger Detail page).

The sheet supports up to two user types. If you need to use this for Activities with more users, email us at support@villagelabs.co.

The activity_timestamp is required if you have an activity_timestamp column in your spreadsheet. It must be in Unix Timestamp format. You can use this website to translate human-readable date/times to unix timestamps, or the input in the furthest to the right column.

You can also delete the activity_timestamp column, in which case Village will consider the time and date that the activities were received through this process as the time and date at which the activities occurred.

Warning: Columns A & B (Status & Response) are used to ensure that already submitted data does not get resubmitted.

If you are adding additional data below already submitted data, and do not want previous rows to be resubmitted, do not delete the Status & Response for previous submissions from Columns A & B.

If you are adding all new data, make sure you delete old Status & Responses from Columns A & B otherwise your new data will not be submitted properly. "

Sheet example:

Warning: do not change the order of the columns, as this will prevent the Apps Script from functioning correctly.

Once you're done testing to ensure you're hitting the API successfully, you may begin using this sheet for submission of real Activities to the Village Activity API.

Warning: all inputs are case sensitive. This means that if your activity_short_id is 'item-sold', activity will not log for this trigger if your activity_short_id is 'Item-Sold'

In this case, 'item-sold' will still successfully log as an activity when you submit it to Village, but it will not associate with any trigger and therefore not activate any rule.

Does a success message in the spreadsheet mean the activity was successfully logged? Not necessarily! A success message received in the sheet does not always means that the activity was logged successfully. There are a few cases - like when you submit the wrong user type associated with a trigger - where the data will be sent successfully to the API, but the activity will not submit successfully because the user type is incorrect.

Therefore, you should always review the API Results table in Side Menu > API > API Results to confirm that your activity successfully submitted.

Using Airtable Automations

Note: You'll need a Airtable Pro plan to access scripting.

You can connect any existing tables you like, but for our purposes we're going to create a simple table that has Activity ID (which will match your Village Trigger Activity ID), Amount, Buyer, and Seller.

For this example we'll pretend we have two different users associated with this activity. Buyer and Seller.

Go to Automations in the top bar. Our goal will be to send new Activity to Village whenever a new record is added to this table, but you can select any trigger you need.

Now select Advanced Logic or Action -> Run Script.

Now you'll want to copy and paste the code snippet below into the Airtable Script Editor (see screenshot).

Airtable Automation Code Snippet to copy/paste. 

// Fetch the record that triggered the automation
let record = input.config().record;

// Variables for Activity Body
let activityID = record.getCellValue("Activity ID");
let amount = record.getCellValue("Amount");
let user1Type = record.getCellValue("User 1 Type");
let user1Email = record.getCellValue("User 1 Email");
let user2Type = record.getCellValue("User 2 Type");
let user2Email = record.getCellValue("User 2 Email");

// Here I'm using a sample timestamp, but in a real scenario you may want to generate this dynamically
let event_timestamp = Math.floor(Date.now() / 1000);  

// API base URL and Endpoint
let apiBaseUrl = "https://api-server-u2blzhjdqa-uc.a.run.app";
let networkID = "YOUR_NETWORK_ID";
let apiUrl = `${apiBaseUrl}/networks/${networkID}/activity`;

// Request Headers
let headers = {
    'Content-Type': 'application/json',
    'Accept': 'application/json',
    'Authorization': 'Bearer API_KEY' // Replace API_KEY with your actual API Key
};

// User Data
let users = {};
users[user1Type.toLowerCase()] = user1Email;
users[user2Type.toLowerCase()] = user2Email;

// Activity Body
let data = {
    "activity_short_id": activityID,
    "amount": String(amount),
    "users": users,
    "metadata": {
        "reference_id": activityID,
        "event_timestamp": event_timestamp
    }
};

// Make a POST request to the API
let response = await fetch(apiUrl, {
    method: 'POST',
    body: JSON.stringify(data),
    headers: headers
});

// Parse JSON response
let responseData = await response.json();

// Check if the request was successful
if (response.status !== 200) {
    console.log('There was an error making the API request.');
    console.log(responseData);
} else {
    console.log(responseData);
}

You'll need to edit two things before running it.

First, change YOUR_NETWORK_ID to the short Network ID, and the YOUR_API_KEY to your API Key. You can find both of these items in Village through Side Menu -> API Keys.

Warning: Ensure that you closely manage access to your Airtable script to keep your API Key secret and secure!

Using HubSpot Workflows

This method uses webhooks in HubSpot's Workflows automation. This requires at minimum the Operations Hub Professional plan.

This section covers sending data and information from HubSpot to Village. We'll cover how to send data from Village to HubSpot separately (eg. how to send information from Village to HubSpot in order to send incentives comms to HubSpot contacts).

HubSpot workflows allow you to create trigger-based rules when defined criteria are met or events happen within HubSpot.

One action that HubSpot can take when a HubSpot trigger occurs is to send data to Village through a webhook. Effectively this allows you to do anything you'd be able to do with Village's APIs in a no-code way, including triggering rules in Village based on activity, updates, or properties in HubSpot such as contacts or deals being created, deals progressing through different stages, sales revenue generated, contact properties being updated, or even updating segments in Village based on HubSpot lists.

Create a workflow

Inside your HubSpot account, navigate to Automation > Workflows > Create workflow

Select the trigger type for your Workflows. If you are triggering rules within Village based on individual user information such as a user being created, changing status, progressing through the funnel or some other characteristic, you're likely going to create a Contact-based workflow, however there are many other workflows you can create around deals, tickets, or conversations.

In this example, we're going to distribute points-based incentives in Village to sales team members based every time they generate a new qualified lead in HubSpot.

Select Contact-based > Blank workflow > Next

We're going to use the contact's lead status changing to a defined value "Open Deal" as the trigger, but there are hundreds of different events, properties or actions you can use as a trigger to send data to Village.

Set up triggers > when an event occurs > Property value changed > Lead status is any of 'Open Deal' > Apply Filter > No, only enroll each contact once (since we only want an incentive to be issued the first time this activity occurs) > Save

Send data to Village when the trigger occurs

Next, we're going to set up a webhook as the action that takes place when the trigger occurs in HubSpot. This webhook will send data to Village's Activity API whenever the trigger occurs in HubSpot.

Click "+" > Choose an action > Workflow > Send a webhook

You'll need to set up your webhook with the following configuration:

Using the Village User Status & Segments APIs with HubSpot

The above example shows the HubSpot webhook configuration for sending activity data to Village through the Activity API. As discussed, you can also create users in Village, and add or remove users from Segments in Village using the same Automations flow in HubSpot. Instead of sending data to the Activity API when a trigger occurs in HubSpot, we're going to send data to Village's User Status API as well as the Segments API.

In this example, we're going to create a user in Village when a new contact is added in HubSpot, and then we're going to add them to a segment in Village called 'New Users'.

The setup is largely the same as above:

Select Contact-based > Blank workflow > Next

We're going to use a CRM object being created as the trigger. We're also going to add a refinement filter to limit the CRM object creation to a new contact.

Set up triggers > when an event occurs > CRM object created > Add refinement filter > Lead Status > is any of "New" > Apply filter > Save filters > No, only enroll each contact once (since we only want an incentive to be issued the first time this activity occurs) > Save

Send data to Village's User Status API when contact is created

This section sends data to Village's User Status API. You can see the correct data format for the User Status API here.

Next, we're going to set up a webhook as the action that takes place when the trigger occurs in HubSpot. This webhook will send data to Village's User Status API (the API that adds and/or invites users to your network in Village) whenever this trigger occurs in HubSpot.

Click "+" > Choose an action > Workflow > Send a webhook

You'll need to set up your webhook with the following configuration. Remember to change the URL to point at the Village User Status API versus the Activity API :

This will create a new user in Village when a new contact is created in HubSpot.

If you also want to send an email invitation for this user to access the Village user dashboard after the user has been created, add an additional webhook action after the first action by clicking the "+" icon below your first webhook action. Then use the same setup as above, but a different status_change value to send_invite.

Send data to Village's Segments API to add contact to a Segment in Village.

This section sends data to Village's Segments API. You can see the correct data format for the Segments API here.

Next, we're going to add this new user to the "New Users" segment we've set up in Village. The Segment ID of this segment is "2".

This webhook will send data to Village's Segments API (the API that adds and/or removes users from a Segment in Village) whenever this trigger occurs in HubSpot.

Click "+" > Choose an action > Workflow > Send a webhook

You'll need to set up your webhook with the following configuration. Remember to change the URL to point at the Segments API:

One important note: adding a user to or removing a user from a Segment using the Segments API overrides any other rule logic created in the admin dashboard in Village. That means that if you use the Segments API to add a user to a Segment, that user will remain in that Segment, even if they qualify for future rules logic that would otherwise remove them from this Segment.

If you do not want rules logic to be ignored after using the Segments API, ensure you send a subsequent 'reset' message to the Segments API. See more here. To do this, add another webhook after the first request to the Segments API:

Click "+" > Choose an action > Workflow > Send a webhook

Then use the following Request body configuration:

More information about using webhooks with HubSpot workflows ->

PreviousNo and Low Code SolutionsNextVillage Docs Feedback Form

Last updated