How to Run Reports from a Custom Contact Field using Google Sheets API Integration in Wix

Who is our client and what problem did they want to solve?

Our client is in the Durable Medical Equipment (DME) industry. They contacted RHM for development help because they wanted a way to view reports based on a custom contact field created as "Sales Rep".

Currently, Wix does not offer a way to view order sales and filter on custom contact fields.

What solution did we provide?

We decided that the best way to help our client with this issue was to create a system where when an order was paid for or refunded, it would fetch the "Sales Rep" value and then dump data about the order that was specified by the customer into Google Sheets using Wix's Backend Order events system and Velo's Google Sheets API integration.

The first step was to re-organize and categorize their existing database of their member's contact information. We did this by adding a dropdown to the member sign-up form to associate each member with an existing Sales Rep. After that, we worked with our client to set up Velo Google Sheets Integration.

After this was set up using Google Cloud Platform and the Wix Secret Manager, we created a sheet for crediting sales reps with paid orders and another for refunded orders since the Google Sheets integration only allows you to send data to a single spreadsheet at a time and not different tabs.

What were the next steps?

Next, we installed the Velo Google Sheets integration in the Code Files tab of the editor, under Packages where it says "Install Velo package".

The next step was to create an "events.js" file in the Backend section of the Code Files tab of the editor. This is the code that gets run whenever an event occurs in the Wix Backend. For this client, it is when an order is paid for or an order was marked refunded.

The code is written so that whenever an order is paid for or refunded, several different fields from the order data are recorded into variables.

Next, the member's sales rep is retrieved by using the Velo Wix CRM Backend 'contacts' module to get the Sales Rep from the contact's custom fields.

If a sales rep is found, then next the code loops through the order's line items and records more information about the specific line items such as the item name, quantity, item price, and total price (recorded as a negative in the case of a refund) and then all of the recorded data is gathered in an array, with one entry per order line item.

This gathered data is then looped through and every entry is appended to the Google sheet (differing on if it was a paid order or a refunded one).

Check out the code we used below:

import { currentMember } from "wix-members";
import { cart } from "wix-stores";
import wixData from "wix-data";
import { fetch } from "wix-fetch";
import wixLocation from "wix-location";

$w.onReady(async function () {
  const multiStateBox = $w("#stateBox");

  const URL = `${wixLocation.baseUrl}/_functions/orderRequest`;

  const currentUser = await currentMember.getMember().then((member) => member);
  const currentCart = await cart.getCurrentCart().then((cart) => cart);

  const customerOptions = await wixData
    .query("SalesRepCustomers")
    .eq("salesRepId", currentUser._id)
    .ascending("company")
    .find()
    .then((results) => {
      return results.items.map((item) => {
        return {
          label: `${item.company} - ${item.firstName} ${item.lastName}`,
          value: item.customerId,
        };
      });
    })
    .catch((err) => {
      console.log(err);
      return [];
    });

  $w("#customerDropdown").options = customerOptions;

  $w("#submitOrder").onClick((event) => {
    const billingAddressInput = $w("#billingAddress").value;
    const shippingAddressInput = $w("#shippingAddress").value;
    const billingSuite = $w("#billingSuite").value;
    const shippingSuite = $w("#shippingSuite").value;

    const billingAddress = {
      addressLine1: `${billingAddressInput.streetAddress.number} ${billingAddressInput.streetAddress.name}`,
      addressLine2: billingSuite,
      city: billingAddressInput.city,
      subdivision: billingAddressInput.subdivision,
      country: billingAddressInput.country,
      postalCode: billingAddressInput.postalCode,
    };

    const shippingAddress = {
      addressLine1: `${shippingAddressInput.streetAddress.number} ${shippingAddressInput.streetAddress.name}`,
      addressLine2: shippingSuite,
      city: shippingAddressInput.city,
      subdivision: shippingAddressInput.subdivision,
      country: shippingAddressInput.country,
      postalCode: shippingAddressInput.postalCode,
    };

    const body = {
      cart: currentCart,
      customerId: $w("#customerDropdown").value,
      salesRep: `${currentUser.contactDetails.firstName} ${currentUser.contactDetails.lastName}`,
      billingAddress,
      shippingAddress,
    };

    const headers = {
      "Content-Type": "application/json",
    };

    multiStateBox.changeState("submitted");

    fetch(URL, { method: "POST", headers, body: JSON.stringify(body) })
      .then((res) => res.json())
      .then((json) => {
        if (typeof json.newOrder !== "undefined") {
          const text = `Successfully created order #${json.newOrder.number}, invoice will be sent to your customer soon.`;
          $w("#successText").text = text;
          multiStateBox.changeState("success");
        } else {
          $w("#failureText").text = json.message;
          multiStateBox.changeState("failure");
        }
      })
      .catch((err) => {
        $w("#failureText").text = err.message;
        multiStateBox.changeState("failure");
      });
  });
});

Have questions about this? Contact us at 225-250-1888 or email robert@roberthebertmedia.com.

About our company

RHM specializes in helping businesses of all sizes and across all industries achieve their digital and web marketing needs. Whether it's designing a new website, building an app, performing custom development, or running Google Ads, our goal is to showcase how you are the best at what you do and help people connect with you. Contact us at 225-250-1888 to get started!