I have recently implemented Google Sheets into my wix site using this video - https://www.youtube.com/watch?v=1ICaE9IR9rQ&t=32s&ab_channel=TheWixWiz - which works perfectly, however, I would prefer if the data could be pulled to sit in a repeater. I would like it to fit the below design if possible, which contains a mixture of text, imgs and a button for a link.
Can someone help me with creating (or editing the current) code that will allow me to do this?
Currently the code that pulls the data in a table format is:
import wixData from 'wix-data'; import wixWindow from 'wix-window'; import {getValuesFromGoogleSheet} from 'backend/google-sheets'; $w.onReady(function () { const populateTable = async ()=>{ const data = await getValuesFromGoogleSheet("A1:F12"); const rows = data.data.values; const headers = rows[0]; let formattedColumns = []; headers.forEach((header)=>{ const column = { "id": header, "dataPath": header, "label": header, "width": 100, "visible": true, "type": "string", } formattedColumns.push(column); }) let formattedRows = []; for(let i = 1; i <rows.length; i++){ const row = {}; for(let j = 0; j < headers.length; j++){ row[headers[j]] = rows[i][j] } formattedRows.push(row); } $w("#googleSheetTable").columns = formattedColumns; $w("#googleSheetTable").rows = formattedRows; } populateTable(); });
Any help is very much appreiated!
(I'm very beginner and had to follow to video step by step)
Thank you. E
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;
});