With the increasing need for remote and collaborative work, the demand for seamless data synchronization between different tools is growing. In this article, we will explore how to integrate Wix, a cloud-based web development platform, with Google Sheets using Google Sheets API and Wix Velo. Watch our video example here.
Step 1: Prepare Your Google Sheets API
Google Sheets API enables you to programmatically read, write, and modify Google Sheets data. Here's how to set it up:
Create a Google Cloud Project: Log into the Google Cloud Console . Create a new project by clicking on the "Select a Project" drop-down menu, then on "New Project."
Enable Google Sheets API: In the new project dashboard, navigate to "Library." Search for 'Google Sheets API' and enable it for your project.
Create Credentials: Go back to the dashboard and click on "Credentials" in the left-hand panel. Click on "Create Credentials" and select "Service Account." Fill in the necessary details, and save the generated JSON file, which includes your client ID and client secret. You will use these credentials in your Wix project.
Share the Google Sheet: Open the Google Sheets document you want to connect with Wix, and share it with the client email found in your JSON file.
Step 2: Set Up Your Wix Velo Environment
Wix Velo is a full-stack development platform that allows you to manage your Wix website's database collections, HTTP functions, and backend code. To set up your Wix Velo environment:
Enable Developer Tools: Open your Wix Editor, navigate to the top menu, click on "Dev Mode", and then select "Enable Velo."
Create a Backend Web Module: In the site structure sidebar, navigate to 'Backend' and then click on '+ New Web Module'. Name this web module (e.g., "googleSheetsIntegration.jsw"). Here you will add your Google Sheets API client credentials and write the function to interact with the Google Sheets API.
Step 3: Write Backend Code to Connect Google Sheets API
In your newly created web module, you'll write backend code to access the Google Sheets API using the credentials you generated earlier. Here's a simple example of how to do this:
import {fetch} from 'wix-fetch';
const serviceAccount = {
// Paste your service account credentials here
};
const spreadsheetId = 'YOUR_SPREADSHEET_ID'; /* Replace with your Google Sheets ID*/
export function getSheetData() {
const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/Sheet1!A1:E5`;
const options = {
method: 'GET',
headers: {
'Authorization': `Bearer ${serviceAccount}`,
'Accept': 'application/json',
}
};
return fetch(url, options)
.then(response => response.json())
.catch(error => console.error('Error:', error));
}
This code will fetch data from the first five rows of your Google Sheets document.
Explanation
Import the Fetch Module: Fetch is a built-in function in modern browsers that provides an easy, logical way to fetch resources asynchronously across the network. Wix Velo's server-side code uses it to make HTTP requests.
import {fetch} from 'wix-fetch';
Add Your Service Account Credentials: In the service account object, input your client ID, client secret, and access token from the JSON file generated when you created your Google Sheets API credentials.
const serviceAccount = {
// Paste your service account credentials here
};
Define Your Google Spreadsheet ID: Replace 'YOUR_SPREADSHEET_ID' with the ID of your Google Sheets document. The ID is the long sequence of characters in the middle of your Google Sheets URL.
const spreadsheetId = 'YOUR_SPREADSHEET_ID'; /* Replace with your Google Sheets ID*/Create a Function to Fetch Data from Google Sheets: We're writing a function called getSheetData() that will fetch data from Google Sheets.
export function getSheetData() {
const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/Sheet1!A1:E5`;
const options = {
method: 'GET',
headers: {
'Authorization': `Bearer ${serviceAccount}`,
'Accept': 'application/json',
}
};
return fetch(url, options)
.then(response => response.json())
.catch(error => console.error('Error:', error));
}
Let's dissect the function:
We're using template literals to insert the spreadsheet ID into the URL. 'Sheet1!A1:E5' represents the range of data we want to fetch — in this case, rows 1 to 5 from columns A to E in the first sheet of the document. Adjust this range based on your needs.
The 'options' object is defined with a method of 'GET' and two headers — 'Authorization' and 'Accept'. The 'Authorization' header includes a Bearer token set to our 'serviceAccount' variable, and 'Accept' is set to 'application/json', indicating that we want the response data in JSON format.
'fetch()' is called with 'url' and 'options' as parameters. 'fetch()' returns a promise that resolves to the response of the request.
We then use .then() to handle the response. If the response is OK, we convert it to JSON format using 'response.json()'.
Lastly, we handle any potential errors using '.catch()'. If there's an error, it will be logged to the console.
Remember to replace the placeholders with actual data. You should now be able to make requests to your Google Sheets from your Wix Velo environment!
Step 4: Use the Backend Function on Your Wix Site
Now that you have established a connection to the Google Sheets API from your Wix Velo environment, you can utilize this connection to display data from your Google Sheets on your Wix website. Below, we will expand on how to use the backend function on your Wix site:
import {getSheetData} from 'backend/googleSheetsIntegration';
$w.onReady(async function () {
try {
const sheetData = await getSheetData();
console.log(sheetData);
// Process and display data on your Wix site here
} catch (error) {
console.error('Error:', error);
}
});
Import the Backend Function: The import statement lets you use the getSheetData function, which we defined in the backend web module ('backend/googleSheetsIntegration'). Remember to replace this path with the path to your actual web module.
import {getSheetData} from 'backend/googleSheetsIntegration';
Use the Backend Function Inside $w.onReady Function: The $w.onReady function runs when the page's HTML has fully loaded, ensuring that the entire document is ready before it attempts to retrieve and display data. We use the 'await' keyword to pause the script until 'getSheetData' has completed its task, and assign the returned data to sheetData.
$w.onReady(async function () {
const sheetData = await getSheetData();
// ...
});
Handle Errors: We use a 'try-catch' block to handle any potential errors that may occur when fetching data. If an error occurs while trying to get the sheet data, it will be caught and logged to the console.
try {
// ...
} catch (error) {
console.error('Error:', error);
}
Display the Data on Your Site: Once you have the data, you can use it to populate elements on your Wix site. For instance, if you want to display the data in a table ($w('#myTable')), you can do:
$w.onReady(async function () {
try {
const sheetData = await getSheetData();
$w('#myTable').rows = sheetData.values; /* Assuming sheetData.values is an array of arrays*/
} catch (error) {
console.error('Error:', error);
}
});
This will fill the table with your Google Sheets data. Depending on how your data is structured and how you want to display it, you will need to adjust this accordingly.
And voila! Your Google Sheets data should now be seamlessly integrated into your Wix website!
Step 5: Real-Time Updates
One of the key benefits of integrating Google Sheets with your Wix site is the ability to have real-time data updates. This means your site visitors always see the most recent and relevant information. This part of our guide will explain how to set up your Wix site to automatically update whenever your Google Sheets data changes.
Before diving in, it's essential to understand that the Google Sheets API doesn't provide real-time data push to your Wix site. Instead, we'll have to set up a polling mechanism on Wix Velo to periodically fetch the latest data from Google Sheets. Here's how to do it:
Define a Refresh Function: Create a function that fetches new data from your Google Sheets and updates your website's UI. For instance, if we were using a table to display the data, the refresh function could look something like this:
async function refreshTableData() {
const sheetData = await getSheetData();
$w('#myTable').rows = sheetData.values;
}
Set Up a Polling Mechanism: Use JavaScript's 'setInterval()' function to periodically run the refresh function. The 'setInterval()' function takes two parameters: the function to run and the interval at which to run it (in milliseconds). Below, we've set it to refresh every 2 seconds (2000 milliseconds):
$w.onReady(async function () {
// Refresh data immediately upon page load
refreshTableData();
// Set up a polling mechanism to refresh data every 2 seconds
setInterval(refreshTableData, 2000);
});
In this example, the data is fetched from Google Sheets and the table is updated every 2 seconds. You can adjust the interval based on your needs, but be cautious not to make requests too frequently as it could potentially lead to rate limiting by the Google Sheets API.
Remember, while this setup ensures your site reflects changes in your Google Sheets in a timely manner, it's not real-time. There will be a delay — as short as your set interval.
In Conclusion
While integrating Wix with Google Sheets using API and Wix Velo might seem challenging at first, we're confident that this comprehensive guide will make the process a whole lot easier for you. Remember, patience is key when working with APIs and coding — don't be discouraged if you don't get it right the first time.
However, we understand that not everyone has the time or expertise to handle this task. That's why we're here to help.
At CodeMasters Agency, we have a team of experienced and dedicated developers ready to assist you. Whether you need help with your current project, or you'd like us to handle the whole integration process for you, we're up for the task. We're experts in Wix Velo and have extensive experience integrating APIs to give your website the functionality it needs.
Don't struggle alone — contact us today! Together, we can transform your Wix website into a data-driven powerhouse connected seamlessly to your Google Sheets.
