Example Site
To see a live example, click here.
The Google Sheets Integration package provides you with the ability to easily monitor and manage data stored in a Google spreadsheet from a Wix site. You can perform the following actions with this package:
To see an example site that demonstrates how to use the package, click here.
Before using the package, set up the following:
velo-spreadsheet-credentials
. Store the contents of the downloaded file in the secret.https://docs.google.com/spreadsheets/d/{YOUR_SHEET_ID}/edit#gid=0
. https://docs.google.com/spreadsheets/d/1Ui3ha9jLRW4mTYBUimt9ooTRyS301SAKpMv_2zYx0n0/edit#gid=0
, the value of your sheet ID is: 1Ui3ha9jLRW4mTYBUimt9ooTRyS301SAKpMv_2zYx0n0
.sheetId
.The following backend files are included in the package. Note that only exported functions that you can use in your site are listed here.
The code in this file contains functions for getting, appending, updating, and clearing data from a Google spreadsheet.
To use the functions below in your backend code, import them with the following syntax:
import { <functionName> } from ‘@velo/google-sheets-integration-backend’;
The file contains the following functions:
getValues()
Queries the spreadsheet for a specified range of cells and returns one or more arrays containing the values stored in those cells.
export async function getValues(sheetId: String, range: String)
appendValues()
Appends the specified values to the spreadsheet either in the current tab in a new row after the last current row, or in a specific location. To append the specified values to a specific location in the spreadsheet, add the optional range
parameter.
export async function appendValues(sheetId: String, values: String[], range: String)
updateValues()
Updates the data in the specified range and dimension of the spreadsheet with the values provided.
export async function updateValues(sheetId: String, values: String[], range: String, dimension: String)
clearValues()
Clears the data in the specified range of the spreadsheet.
export async function clearValues(sheetId: String, range: String)
sheetId
: ID of the spreadsheet you want to use. You can find the ID string in the URL of your spreadsheet link: https://docs.google.com/spreadsheets/d/{YOUR_SHEET_ID}/edit#gid=0
.values
: Array of strings you want to insert into the spreadsheet.range
: The location in the spreadsheet you want to select. String must be in ‘A1’ notation. For example, to select cells A1:B1 in the current tab, use ‘A1:B1’
. To select cells A1:B1 in Tab2 of the spreadsheet, add the tab name before the cells and separate it with an exclamation mark. For example, ‘Tab2!A1:B1’
.dimension
: The type of your range. String is either ‘ROWS’
or ‘COLUMNS’
.This section demonstrates how you can work with the package, and the different options for using the package functions. There are 4 main use cases you can implement with this package:
The following describes how you can use this package to get the data from the first row (A1:B1
) of your spreadsheet. If you want to get data from a different location in your spreadsheet, simply pass a different range
to the getValuesWrapper()
function located in the getValuesFromSheet()
function in your page code.
Open your Google spreadsheet and place some data in the first row (A1:B1
):
To use the getValues()
function in your page code, add a web module to your backend. We’ll call ours googlesheet-wrapper.jsw. Include the following code in this file:
import { getValues } from "@velo/google-sheets-integration-backend";
import wixSecretsBackend from "wix-secrets-backend";
async function getSecretSheetId() {
const id = await wixSecretsBackend.getSecret("sheetId");
return id;
}
export async function getValuesWrapper(range) {
try {
validateRange(range);
try {
const sheetId = await getSecretSheetId();
const result = await getValues(sheetId, range);
const response = result.data.values;
return response;
} catch (err) {
return Promise.reject("Get values failed. Info: " + err);
}
} catch (validationError) {
return Promise.reject(validationError.toString());
}
}
function validateRange(range) {
const regex = /([a-zA-Z0-9:!]+)/g;
const match = range.match(regex);
if (!match || match.length !== 1 || match[0] !== range) {
throw new Error(`Input range is invalid (got: ${range})`);
}
}
Add the following page elements to your site:
Import the getValuesWrapper()
function from the web module to your page code and run it when the button is clicked.
import { getValuesWrapper } from "backend/googlesheet-wrapper.jsw";
$w.onReady(function () {
registerHandlers();
});
function registerHandlers() {
$w("#getButton").onClick(() => getValuesFromSheet());
}
async function getValuesFromSheet() {
try {
const [name, email] = (await getValuesWrapper("A1:B1"))[0];
$w("#nameOutput").value = name;
$w("#emailOutput").value = email;
} catch (err) {
showMessage(err.toString());
}
}
function showMessage(msg) {
$w("#showMsg").text = msg;
$w("#showMsg").expand();
setTimeout(() => {
$w("#showMsg").collapse();
}, 5000);
}
The following describes how you can use this package to add data to the next empty row of your spreadsheet.
Note: The appendValues()
function appends data to your spreadsheet linearly. If there are blank cells in between data cells, the function may not work as expected.
To use the appendValues()
function in your page code, add a web module to your backend. We’ll call ours googlesheet-wrapper.jsw. Include the following code in this file:
import { appendValues } from "@velo/google-sheets-integration-backend";
import wixSecretsBackend from "wix-secrets-backend";
async function getSecretSheetId() {
const id = await wixSecretsBackend.getSecret("sheetId");
return id;
}
export async function appendValuesWrapper(values) {
try {
validateValues(values);
try {
const sheetId = await getSecretSheetId();
const result = await appendValues(sheetId, values);
const response = result.data.updates.updatedRows + " rows were appended";
return response;
} catch (err) {
return Promise.reject("Append values failed. Info: " + err);
}
} catch (validationError) {
return Promise.reject(validationError.toString());
}
}
function validateValues(values) {
for (const val of values) {
if (typeof val !== "string") {
throw new Error(
`Input value's type must be a string (got: ${val}, ${typeof val})`,
);
}
}
}
Add the following page elements to your site:
Import the appendValuesWrapper()
function from the web module to your page code and run it when the button is clicked.
import { appendValuesWrapper } from "backend/googlesheet-wrapper.jsw";
$w.onReady(function () {
registerHandlers();
});
function registerHandlers() {
$w("#appendButton").onClick(() => saveValuesToSheet());
}
async function saveValuesToSheet() {
const name = $w("#nameInput").value;
const email = $w("#emailInput").value;
const values = [name, email];
try {
const res = await appendValuesWrapper(values);
$w("#nameInput").value = "";
$w("#emailInput").value = "";
showMessage(res);
} catch (err) {
showMessage(err.toString());
}
}
function showMessage(msg) {
$w("#showMsg").text = msg;
$w("#showMsg").expand();
setTimeout(() => {
$w("#showMsg").collapse();
}, 5000);
}
The following describes how you can use this package to update the data in the first row of your spreadsheet (A1:B1
). If you want to update data from a different location in your spreadsheet, simply pass a different range
and dimension
to the updateValuesWrapper()
function located in the updateValuesOnSheet()
function in your page code.
To use the updateValues()
function in your page code, add a web module to your backend. We’ll call ours googlesheet-wrapper.jsw. Include the following code in this file:
import { updateValues } from "@velo/google-sheets-integration-backend";
import wixSecretsBackend from "wix-secrets-backend";
async function getSecretSheetId() {
const id = await wixSecretsBackend.getSecret("sheetId");
return id;
}
export async function updateValuesWrapper(values, range, dimension) {
try {
validateValues(values);
validateRange(range);
validateDimension(dimension);
try {
const sheetId = await getSecretSheetId();
const result = await updateValues(sheetId, values, range, dimension);
const response = result.data.updatedCells + " cells were updated";
return response;
} catch (err) {
return Promise.reject("Update values failed. Info: " + err);
}
} catch (validationError) {
return Promise.reject(validationError.toString());
}
}
function validateValues(values) {
for (const val of values) {
if (typeof val !== "string") {
throw new Error(
`Input value's type must be a string (got: ${val}, ${typeof val})`,
);
}
}
}
function validateRange(range) {
const regex = /([a-zA-Z0-9:!]+)/g;
const match = range.match(regex);
if (!match || match.length !== 1 || match[0] !== range) {
throw new Error(`Input range is invalid (got: ${range})`);
}
}
function validateDimension(dimension) {
if (dimension !== "ROWS" && dimension !== "COLUMNS") {
throw new Error(
`Input dimension must be either 'ROWS' or 'COLUMNS'(got: ${dimension})`,
);
}
}
Add the following page elements to your site:
Import the updateValuesWrapper()
function from the web module to your page code and run it when the button is clicked.
import { updateValuesWrapper } from "backend/googlesheet-wrapper.jsw";
$w.onReady(function () {
registerHandlers();
});
function registerHandlers() {
$w("#updateButton").onClick(() => updateValuesOnSheet());
}
async function updateValuesOnSheet() {
const name = $w("#nameInput").value;
const email = $w("#emailInput").value;
const values = [name, email];
try {
const res = await updateValuesWrapper(values, "A1:B1", "ROWS");
$w("#nameInput").value = "";
$w("#emailInput").value = "";
showMessage(res);
} catch (err) {
showMessage(err.toString());
}
}
function showMessage(msg) {
$w("#showMsg").text = msg;
$w("#showMsg").expand();
setTimeout(() => {
$w("#showMsg").collapse();
}, 5000);
}
The following describes how you can use this package to clear a range of data from your spreadsheet:
To use the clearValues()
function in your page code, add a web module to your backend. We’ll call ours googlesheet-wrapper.jsw. Include the following code in this file:
import { clearValues } from "@velo/google-sheets-integration-backend";
import wixSecretsBackend from "wix-secrets-backend";
async function getSecretSheetId() {
const id = await wixSecretsBackend.getSecret("sheetId");
return id;
}
export async function clearValuesWrapper(range) {
try {
validateRange(range);
try {
const sheetId = await getSecretSheetId();
const result = await clearValues(sheetId, range);
const response = "Range cleared: " + result.clearedRange;
return response;
} catch (err) {
return Promise.reject("Clear values failed. Info: " + err);
}
} catch (validationError) {
return Promise.reject(validationError.toString());
}
}
function validateRange(range) {
const regex = /([a-zA-Z0-9:!]+)/g;
const match = range.match(regex);
if (!match || match.length !== 1 || match[0] !== range) {
throw new Error(`Input range is invalid (got: ${range})`);
}
}
Add the following page elements to your site:
Import the clearValuesWrapper()
function from the web module to your page code and run it when the button is clicked.
import { clearValuesWrapper } from "backend/googlesheet-wrapper.jsw";
$w.onReady(function () {
registerHandlers();
});
function registerHandlers() {
$w("#clearButton").onClick(() => clearValuesFromSheet());
}
async function clearValuesFromSheet() {
const range = $w("#range").value;
try {
const res = await clearValuesWrapper(range);
$w("#range").value = "";
showMessage(res);
} catch (err) {
showMessage(err.toString());
}
}
function showMessage(msg) {
$w("#showMsg").text = msg;
$w("#showMsg").expand();
setTimeout(() => {
$w("#showMsg").collapse();
}, 5000);
}
This Velo package uses the following npm package. To view the npm license, see the npm readme.
1.0 Initial version.
google, spreadsheet, googleapis