Maybe this will help.
The function getValuesFromSheet() will return ALL the data in your spreadsheet and the returned data will be in a JSON format mapping the header values to the data values and an _id added to each record.
Create a googlesheets.JSW file that looks like this:
import { getValues } from '@velo/google-sheets-integration-backend';
import wixSecretsBackend from 'wix-secrets-backend';
export async function getValuesFromSheet() {
// Get the sheet ID from the secrets backend
const sheetid = await wixSecretsBackend.getSecret('sheetid');
// Retrieve values from the Google Sheet
const results = await getValues(sheetid, 'A1:Z');
// console.log("Results: ")
// console.log(results)
// Trim and convert header values to lowercase
const header = results.data.values[0].map((key) => key.trim().toLowerCase().replace(/\s+/g, ''));
// console.log("Header: ")
// console.log(header)
// Extract data rows
const data = results.data.values.slice(1);
// Map data rows to records with unique identifiers (_id)
const records = data.map((row, index) => {
const record = Object.fromEntries(header.map((key, i) => [key, row[i]]));
return { _id: index.toString(), ...record };
});
// console.log(records)
return records;
}
So a sample return of data from a spreadsheet that had the columns LAST NAME, FIRST NAME, HOME PHONE, CELL PHONE, EMAIL ADDRESS would look like :
[
{
"_id" : "0",
"lastname": "Flintstone",
"firstname": "Fred",
"homephone": "999-123-1234",
"cellphone": "999-999-9999",
"emailaddress": "fred@gmail.com",
},
{
"_id" : "1",
"lastname": "Rubble",
"firstname": "Barney",
"homephone": "888-888-8888",
"cellphone": "888-123-4567",
"emailaddress": "barney@gmail.com",
}
]
Call the function from your frontend like this:
The frontend page is called GoogleSheet....
import {getValuesFromSheet} from 'backend/googlesheets.jsw'
$w.onReady(async function () {
// Set up repeater item ready event handler
$w('#repeaterGoogleSheet').onItemReady(($item, itemData, index) => {
$item('#textFirstName').text = itemData.firstname;
$item('#textLastName').text = itemData.lastname;
$item('#textCellPhone').text = itemData.cellphone;
});
// Retrieve data from the roster
const repeaterData = await getValuesFromSheet();
// console.log("Repeater Data: ")
// console.log(repeaterData)
// Set repeater data
$w("#repeaterGoogleSheet").data = repeaterData;
});