Example: Google Sheets NPM

Show and tell
,
101

@crookedcreekartleagu Did you figure this out. I am facing the same problem.

102

@bharatjaglan Sadly, I have not gotten it to work. None of the suggestions resolved it for me.

103

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!

104

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.

566×626 14.8 KB

blue button, but nothing happens when clicked.

1920×485 39.8 KB

Firefox console after button is clicked.

1920×483 20 KB

Network activity after button is pressed, as you can see, there is no request happening.

Any help will be greatly appreciated.

105

Try to debug the button event , see if it works. If not remove the event and create it again from the editor.

106

Done, still not working. I then tried wiping the database in the new webpage and tried to reconfigure it, making sure all the code from the configuration page is copied exactly. However, now in the configuration page of the new website, none of the Done vector Art are showing and the get code button is disabled. What do you think is happening?

107

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

108

Okay, I tried to debug using the wix console debugger, but it says that there is nothing to log, even when I click the button. However, in the chrome console there are some errors that I don’t understand.

1366×235 5.78 KB

1366×539 25.5 KB

109

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.

110

Great, it works now. What I had to do was remove:

if ($w(‘#firstNameTxt’).validity.valid === false )
return ;

if ($w(‘#lastNameTxt’).validity.valid === false )
return ;

Is a bad thing if I leave it like this and publish it?

111

Any idea on how I can see the code/backend ?

112

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?

113

@tonyleehann

**I’m having the same issue where the Save Button is disabled. **

I’m new to coding, but from what I understand, that means the database is not configured?

I noticed that the button is enabled depending on the “config” web module, and that in the “config” web module, it says “require” is not defined.

I followed the same steps as Leo mentioned:

  1. Completed the configuration phase on the configuration page in the example website. (I’ve tested it on the example site’s home page and it works).
  2. Installed the googleapis node module in backend
  3. Created a config.jsw file and googleSheet.jsw file in backend and copied the relevant JS code from the example site.
  4. Recreated and properly labeled and set events for the UI components (2x text box and save button) in new site. Then copied the JS code from the example site’s home page and pasted into the new site’s page.
  5. Created a new database collection with the same name and settings as the example site and entered in the relevant fields.

I also made sure the database permissions were set correctly like @Gal Morad mentioned, and I tried publishing the site. Still not working.

Tony, you mention adding 3 missing rows of “refresh token” “sheet id” and “client config” and their values. Where can I find their values?

Also, does this mean the user input will not be saved into the Wix database collection? Or will new submissions just create new rows?

114

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

558×910 29.3 KB