#Example #OAuth #GoogleSheets #GoogleAPIs #WebModules #ServerSide #backend #ServerSide #3rdPartyServices #PackageManager #ExternalCode #NPM
Demonstrates
Using External Code Libraries in Wix Code using the NPM Package Manager
Using OAuth 2.0 to Access Google APIsWeb modules - server-side (backend) code
Accessing 3rd Party Services (using backend function)
Calling Server-side Code from the Front-end with Web Modules
Required for this example
You will need a Google account for a Google Sheet.
Links for this example
Open the Example template in the Wix Editor
About this example
This example uses the Google APIs Node.js Client package included in the Wix Package Manager to access a Google Sheet. Using built-in code packages is easier and more convenient than having to write your own interface code.
You will need a Google Account where you can create a Sheet to be used as the “target" of the app. The Home page of this app is a form on which the user can enter data that will be saved to the Google Sheet. Before the Home page form can be used, you will need to configure the app to access the Sheet.
Google APIs use the OAuth 2.0 protocol for authentication and authorization. The Configuration page provides a step-by-step process for you to configure the app to access a Google Sheet. At the end of the configuration process, the form on the Home page can be filled in and the data saved to the Sheet.
Thanks to my Wix colleague Gal who provided the original POC.
I tried the example and it works fine. Some points:
In the first step, "Enable the Google Sheets API", I chose Desktop App. I did this since I don't have a link to redirect upon authorization.
After entering in the Customer Configuration and the Sheet URL, make sure you hit Enter. You should get a green checkmark after entering in each field.
The Get Code button will be enabled when both fields have a green checkmark.
Then, copy the authorization code into the final field - don't forget to hit Enter.
You will now have another green checkmark and can now use your Google Sheet.
Eazy Peazy
As a note the default template does not see to work anymore.
Getting the client configuration provides this:
{"web":{"client_id":"a_long_hash.apps.googleusercontent.com","project_id":"quickstart-a-number","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/token","auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs","client_secret":"this-is-secret","javascript_origins":["http://www.secret"]}}
TypeError: Cannot destructure property `client_secret` of 'undefined' or 'null'.
and in the chrome console: wixcode-worker.js:18 TypeError: Cannot destructure property `client_secret` of 'undefined' or 'null'.
at createClient (backend/common-googleSheet.jsw:29:8)
at processTicksAndRejections (internal/process/task_queues.js:93:5) At it there were some mis-labels in the common-googleSheet.jsw based on the google-provided credentials-file. I renamed them in the json- for ease: web=> installed, javascript_origins=>redirect_uris,
Unpacking it more slowly in common-googleSheet.jsw : const client_id = credentials.installed.client_id const client_secret = credentials.installed.client_secret const redirect_uris = credentials.installed.redirect_uris console.log(client_id) console.log(client_secret) console.log(redirect_uris)
Still yielded odd output wit redirect_uris going to null. Needless to say, this did not work. What should I do?
Any idea on how I can see the code/backend ?
You need to add console messages in each step, if the first one does not work you need to create the event again from the button properties.
You need to debug it step by step using messages https://www.wix.com/corvid/forum/corvid-tips-and-updates/you-can-log-console-messages-in-your-backend-code
Try to debug the button event , see if it works. If not remove the event and create it again from the editor.
Hello,
I have followed all the steps from the all the previous comments, and I have a problem I have not read. My button works, it is blue, but when I click on it, nothing happens. All the code has been copied, the database created exactly. I would appreciate any help.
code for commo-googleSheets:
import wixData from 'wix-data'; const { google } = require("googleapis"); let options = { "suppressAuth": true // suppress db auth to allow secure db permissions }; export async function getSheetId() { const response = await wixData.query("config").eq('title', 'sheet id').find(options); if (response.items.length === 0) return null; return response.items[0].value; } export async function getTokens() { const response = await wixData.query("config").eq('title', 'refresh token').find(options); if (response.items.length === 0) return null; return JSON.parse(response.items[0].value); } export async function getClientConfig() { const response = await wixData.query("config").eq('title', 'client config').find(options); if (response.items.length === 0) return null; return response.items[0].value; } export async function createClient() { let val = await getClientConfig(); let credentials = JSON.parse(val); const { client_secret, client_id, redirect_uris } = credentials.installed; return new google.auth.OAuth2(client_id, client_secret, redirect_uris[0]); }
code for config.jsw
// Filename: backend/config.jsw (web modules need to have a .jsw extension) import wixData from 'wix-data'; const { google } = require("googleapis"); import { createClient, getSheetId, getTokens } from 'backend/common-googleSheet' const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; let options = { "suppressAuth": true // suppress db auth to allow secure db permissions }; export async function clearConfig() { let response = await wixData.query("config").eq('title', 'client config').find(options); await wixData.remove("config", response.items[0]._id, options); response = await wixData.query("config").eq('title', 'sheet id').find(options); await wixData.remove("config", response.items[0]._id, options); response = await wixData.query("config").eq('title', 'refresh token').find(options); await wixData.remove("config", response.items[0]._id, options); } export async function isConfig() { let response = await wixData.query("config").eq('title', 'client config').find(options); if (response.items.length === 0) return false; let cc = (response.items[0].value) ? true : false; response = await wixData.query("config").eq('title', 'sheet id').find(options); if (response.items.length === 0) return false; let id = (response.items[0].value) ? true : false; response = await wixData.query("config").eq('title', 'refresh token').find(options); if (response.items.length === 0) return false; let tk = (response.items[0].value) ? true : false; return (cc && id && tk); } export async function client(config) { let response = await wixData.query("config").eq('title', 'client config').find(options); if (response.items.length === 0) { const toInsert = { "title": "client config", "value": config }; response = await wixData.insert("config", toInsert, options); return; } let items = response.items; let item = items[0]; const toUpdate = { "_id": item._id, "title": "client config", "value": config }; return await wixData.update("config", toUpdate, options); } export async function sheeetId(url) { var regex = new RegExp('/spreadsheets/d/([a-zA-Z0-9-_]+)'); let result = url.match(regex); let response = await wixData.query("config").eq('title', 'sheet id').find(options); if (response.items.length === 0) { const toInsert = { "title": "sheet id", "value": result[1] }; response = await wixData.insert("config", toInsert, options); return; } let items = response.items; let item = items[0]; const toUpdate = { "_id": item._id, "title": "sheet id", "value": result[1] }; return await wixData.update("config", toUpdate, options); } export async function getAuthUrl() { const oAuth2Client = await createClient(); const res = oAuth2Client.generateAuthUrl({ access_type: 'offline', scope: SCOPES, }) return Promise.resolve(res); } export async function generateTokens(offlineCode) { const authClient = await createClient(); return authClient.getToken(offlineCode, async (err, tokenJson) => { if (err) { console.log(err); } else { let response = await wixData.query("config").eq('title', 'refresh token').find(options); if (response.items.length === 0) { const toInsert = { "title": "refresh token", "value": JSON.stringify(tokenJson) }; response = await wixData.insert("config", toInsert, options); return; } let items = response.items; let item = items[0]; const toUpdate = { "_id": item._id, "title": "refresh token", "value": JSON.stringify(tokenJson) }; const ret = await wixData.update("config", toUpdate, options); } }) }
code for googleSheets
import wixData from 'wix-data'; const { google } = require("googleapis"); import { createClient, getSheetId, getTokens } from 'backend/common-googleSheet' let options = { "suppressAuth": true // suppress db auth to allow secure db permissions }; async function createAuthorizedClient() { try { const oAuth2Client = await createClient(); let tokens = await getTokens(); oAuth2Client.setCredentials(tokens); // const accessToken = await oAuth2Client.refreshAccessToken(tokens); // deprecated const accessToken = oAuth2Client.getAccessToken(); // replaces deprecated API call oAuth2Client.getTokenInfo(accessToken); // checks validity of tokens return oAuth2Client; } catch (err) { console.log("failed to refreh token" + err); } } export async function saveFormData(values) { const authorizedAuthClient = await createAuthorizedClient(); const sheetId = await getSheetId(); return insertRow(values, authorizedAuthClient, sheetId); } function insertRow(values, authClient, ssID) { var sheets = google.sheets('v4'); const request = { spreadsheetId: ssID, // The ID of the spreadsheet to update. valueInputOption: 'RAW', // How the input data should be interpreted. insertDataOption: 'INSERT_ROWS', // How the input data should be inserted. range: 'A1:A2', // The A1 notation of a range to search // for a logical table of data. Values will be // appended after the last row of the table. resource: { "values": [ values ] }, auth: authClient, }; try { return sheets.spreadsheets.values.append(request, function (err, response) { if (err) { console.log("error in append:" + err); return; } return "ok"; }); } catch (err) { console.log("error in append values" + err); } }
code for the Home page
import { saveFormData } from 'backend/googleSheet' import { isConfig } from 'backend/config' $w.onReady(async function () { let config = await isConfig(); if (config) { $w('#btnSaveForm').enable(); $w('#promptConfig').hide(); } else { $w('#btnSaveForm').disable(); $w('#promptConfig').show(); } }); export function btnSaveForm_click(event, $w) { if ($w('#firstNameTxt').validity.valid === false) return; if ($w('#lastNameTxt').validity.valid === false) return; saveFormData([$w("#firstNameTxt").value, $w("#lastNameTxt").value]).then(() => { $w('#firstNameTxt').value = ""; $w('#lastNameTxt').value = ""; $w('#savedMessage').show(); $w('#firstNameTxt').resetValidityIndication(); $w('#lastNameTxt').resetValidityIndication(); }) .catch(error => { console.log(error); }); } export function firstNameTxt_keyPress(event) { $w('#savedMessage').hide(); } export function lastNameTxt_keyPress(event) { $w('#savedMessage').hide(); }
the config database content has been correctly copy pasted, even downloaded as a csv and then imported.
blue button, but nothing happens when clicked.
Firefox console after button is clicked.
Network activity after button is pressed, as you can see, there is no request happening.
Any help will be greatly appreciated.
hey! thanks @Yisrael (Wix)& @Gal Morad for the method it's working!
I just wanted to ask how can i add checkbok to the form and make it run. because i've strrugling with these for weeks without any progress please any help ?
i tried to add the check box Value to the code but it didn't work .
and also i wanted to make some not required i mean just it's ok to not choose and others with required check at least one option.
Thanks!
@Yisrael (Wix)
@leopoldtaylor - You were having the same problem right?
Why isn't the get code button clickable? /I have also attached the error from chrome developer.
Hi, can someone please help me get started with this? I am fairly tech savvy but a js code newbie. When I open the example everything appears to work and I can put in the data for the first two fields and get checkmarks, but the "Get Code" button never gets enabled. What am I doing wrong? Thanks! Barbara
This is great! Thank you for this. I have successfully gotten my data from Wix into a Google Sheet. I have two waivers that I have the user check off. When they submit the form I get the full "Media Waiver" instead of a simple checkbox in Google Sheets, whereas in the Wix Database, I see checkmarks. Small thing, but since I have other admins checking the spreadsheet, I'd love to replace the words "Media Waiver" with just a simple ✔. Thanks for the help!
Hi, I need to create a dynamic chart from my database with filters. I tried ( https://www.vorbly.com/Vorbly-Code/WIX-IFRAME-GRAPHS-%26-CHARTS-WITH-FILTERS ) but that's not working for some reason. Please help.
Hi @Yisrael (Wix)
Awesome !! creativity.
Can you please help me resolving below issue : I am getting error "Cannot read property 'installed' of null" TypeError: Cannot read property 'installed' of null
TypeError: Cannot read property 'installed' of null
Cannot read property 'installed' of null common-googleSheet.jswLine 29
Ok, thanks. Do you have an idea why "Save" button is not responding?
Can you elaborate about the problem?
Hi @Yisrael (Wix) , @Gal Morad . I also have an issue with "Get Code" Button. The link to the site: Do you have any ideas? Thanks.
hey,
can you Add GID so that we can update a specific sheet, not just the very first sheet
The configuration form saves this data into a data base, you need to create data base at your site that will be the same as the db in the example.
thanks for your response.
still need to understand,
Are you are referring to the credentials file i download?
if yes where do i put this info in my own website.
thanks
hi Yisrael
i was able to follow your test site and got it working.
However could you show in more detail how to do this on my own site thats already built.
i copied all the backend files already as well as added the googleapis.
which parts on the code to i need to customize to my google sheet ID etc and reference my own form in the code.
Thanks
Sholom