Example: Google Sheets NPM

Show and tell
,
41

hi,
thanks for great example.

how i can add timestamp for every submit?

I have tried find solution, i tried to write in google script, but this is not working, working only manual input in google sheets.

I be extra thankful, when you find time to help me.

42

Why not just add a type stamp field to the Sheet and save that field along with the other fields, when saving to the spreadsheet.

43

@yisrael-wix I found script from stackoverflow made by Sandy Good :

function addTimeStamp(e) {
  var typeOfChange = e.changeType;

  //Logger.log('typeOfChange: ' + typeOfChange);

  //Logger.log('typeof typeOfChange: ' + typeof typeOfChange);

  if (typeOfChange === "INSERT_ROW") {
    var timeStamp = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    var activeRangeIs = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
    //Logger.log(activeRangeIs);

    var whatRow = activeRangeIs.getRow();

    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(whatRow, 3).setValue(timeStamp);
  };

};

Then use an installable trigger.
I hope this script help other like me and thanks again @Yisrael (Wix) for great post.
R

44

@Yisrael (Wix) I was able to successfully utilize your example to connect to Google Drive. Thank you!

I have ran into a slight issue though. This may be more Google API related but I thought I would ask.

I have the following code that creates a folder. The folder is created successfully, however, the file.id is always null.

I would be grateful for your thoughts on why this may be happening.

export async function createFolder(folderName) {
    const authorizedAuthClient = await createAuthorizedClient();
 
    const drive = google.drive({version: 'v3', auth: authorizedAuthClient });

     let fileMetadata = {
         'name': folderName,
         'mimeType': 'application/vnd.google-apps.folder',
         parents: ['redacted']
     };

    let toCreate = {
        resource: fileMetadata,
        fields: 'id, name'
    };

    drive.files.create(toCreate, (err, file) => {
         if (err) {
            // Handle error
            console.error(err);
        } else {
            console.log('Folder ID: ', file.id);
        }
    });
}

Thanks,
Peter

45

try this

function insertRow(values, authClient, ssID) {
values.push( new Date().toString());

46

Hi @yisrael-wix I tried your example template and it works of course.
Could you explain better how does it work please ?
Also in block diagram, it could be really motivating for who like me it’s trying to learn.

I would learn how import a CSV file into a Wix collection via code in order to refresh and update the old data in Wix dbase, so I thought to get it via Google sheet.

Thx in advance
Mauro

47

For the newbies like me: actually I found a good reference via node-googleapis-installation in Wix, the following link, where all is really well explained:

https://github.com/googleapis/google-api-nodejs-client#readme
or better
https://apis-nodejs.firebaseapp.com/oauth2/index.html#typescript

https://developers.google.com/identity/protocols/OpenIDConnect

https://developers.google.com/oauthplayground/

More, I found also that you could realize an automatic Service-Service Authentication !!

48

It looks like you found your way to get the information.
this example is an adaptation to corvid of google sheet nodejs quick start.
https://developers.google.com/sheets/api/quickstart/nodejs

As mentioned above in this example the configuration and usage were separated.
the configuration form generates the needed tokens for authentication and communication with google sheet api. It saves these token to the db.

At the home page you can find the user’s form. this form is using the authentication tokens when
entering data to the sheet.

49

@gal-morad thanks so much ! I am trying to learn and practice, with difficulty, but my goal is import via code a csv file in a Wix collection. I thought of import a csv file in a Google Sheet and then import via Json in Wix.
How do you see this ?

50

@mauro-vento-avyno I am not sure I understand what you are trying to achieve.
you can import and export csv using the db manager.
https://support.wix.com/en/article/importing-data-to-a-database-collection
you also have example for doing it with code.
https://support.wix.com/en/article/corvid-tutorial-importing-and-exporting-collection-data-with-code

51

@gal-morad you are in right :slight_smile:
I usually store data in google cloud Sheet
Wix node doesn’t have the fs readfile and I would create an automatization to load in Wix collection the google data, without the manual dB manager.

52

Hi Peter I find your Solved question on the Net. I would push it here for the people with the same problem.
Answer:

file.id —->. file.data.id

53

Can someone help a total beginner to get started, here? I am technically savvy, but brand new to Corvid and Wix automation, and lacking context for this post. How does one access the “Google Sheet NPM” form, in the OP?

54

This example demonstrates how to implement google sheet integration from scratch at your site.
Begin by clicking “Example Template” it will open editor with a code and DB, if you publish it and follow the configurations step, you will have a standalone form that enters rows into a google sheet.
you can learn from this code how to implement it at your site.

55

One question, is possible update on a Google sheet a collection of 500 or 1000 records with this method ?

56

Looks like this is Google API question, you can check it here:
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append .
from corvid point of view, there is no limitation for 500 or 1000 rows.

57

@leopoldtaylor For those people stuck with the same problem (you will know because the Save Button will be grayed out which gives you a clue if you read the js code) - The database is not configured.

  • in step 5 from Leo’s steps above

…

  1. Created a new database collection with the same name and settings as the example site and entered in the relevant fields.

Yes, you created a new database, but what happened was it was linked/created on the new WIX site and not on the WIX site you are trying to put the form onto.

TL:DR to resolve, go into the database settings of your WIX site (not the new one) and add the 3 missing rows of “refresh token” “sheet id” and “client config” and their values.

1010×1016 41.2 KB

58

I am new to Wix and i am not a coder. I did the example - it worked GREAT. I do have a question though. How do i use this example in my website? I am using Wix to build an APP that will collect data from the user. This data is saving in the Database collection i created inside Wix. How do i make my submit button - connect to my google sheet instead of using the database collection?

59

Would be really useful to see another great example like this for the Google Calendar API! Thanks

60

Hi dear @ Yisrael (Wix) ,
Thanks for the great code. It saves lots of time. :slight_smile:
But still I have a question.
After added the data to the google sheet (using your code) , is there any way to update it through the website…?
I mean using a dropdown menu, select & retrieve the data and update the selected data field.

Thanks in advance.
Chathuranga.