1.0.1 • Published 2 years ago
@404invalid-user/googlesheetsdb v1.0.1
google sheetsdatabase
get and add data to google sheets easily using objects
googlesheetsdb is a package for the googleapis package that makes it more like the mongoose package when getting and adding data.
setup
assuming you have already gone to https://docs.google.com/spreadsheets and made a sheet that looks some what like this
const Googlesheetsdb = require('@404invalid-user/googlesheetsdb');
const sheetid = '1aFHYCF_2QyzSRQDyICWy--CWOXbbvDr7SwY4uJWcMoM' //this will be your sheet id you can find this in the url
const page = 'Sheet1' //the page in your sheet, you can find and edit these at the bottom of your sheets
const idname = 'id' //the name of your id cell (defaults to id)
//make a new instance and pass the details though
const sheet = new Googlesheetsdb.sheet(sheetid, page, idname);
//path to the json file you got from the google api console
//this will be relative to the root of your project (where node_modules are)
const appAuthDetailsPath = './tokens.json';
//now you authenticate with google sheets
sheet.setup(appAuthDetailsPath).catch(error => {
console.log("something has gone wrong: " + error.stack || error)
});
docs
.getbyid() - get a row by the id cell name and returns an object
example
const Googlesheetsdb = require('@404invalid-user/googlesheetsdb');
const sheet = new Googlesheetsdb.sheet('1aFHYCF_2QyzSRQDyICWy--CWOXbbvDr7SwY4uJWcMoM', 'Sheet1');
sheet.setup('./tokens.json');
async function GetPerson() {
const person = await sheet.getbyid('1');
console.log("name: " + person.name);
console.log("age: " + person.age);
}
GetPerson();
( await .getbyid()).save() - saves the changes you make to that object
example
const Googlesheetsdb = require('@404invalid-user/googlesheetsdb');
const sheet = new Googlesheetsdb.sheet('1aFHYCF_2QyzSRQDyICWy--CWOXbbvDr7SwY4uJWcMoM', 'Sheet1');
sheet.setup('./tokens.json');
async function UpdateAge() {
const person = await sheet.getbyid('1');
console.log("name: " + person.name);
console.log("age: " + person.age);
//return is a string so number operations wont work
person.age = (parseInt(person.age)++).toString();
person.save().catch(error => {
console.log("error saving person likely no edit permission: " + error.stack || error);
});
//see your google fomrs it should have updated unless your application doesn't have edit permission
}
UpdateAge();
.getall() - gets all columns and returns an array of objects
example
const Googlesheetsdb = require('@404invalid-user/googlesheetsdb');
const sheet = new Googlesheetsdb.sheet('1aFHYCF_2QyzSRQDyICWy--CWOXbbvDr7SwY4uJWcMoM', 'Sheet1');
sheet.setup('./tokens.json');
async function GetPeople() {
const people = await sheet.getall();
console.log("people: " + JSON.stringify(people));
//get first person in array
const person = people[0];
console.log("name: " + person.name);
console.log("age: " + person.age);
}
GetPeople();
.create() - inserts new object to google sheets
example
const Googlesheetsdb = require('@404invalid-user/googlesheetsdb');
const sheet = new Googlesheetsdb.sheet('1aFHYCF_2QyzSRQDyICWy--CWOXbbvDr7SwY4uJWcMoM', 'Sheet1');
sheet.setup('./tokens.json');
async function NewPerson() {
const jon = {id: '4', name: 'jon', age: 23};
sheet.create(jon).catch(error => {
console.log("error creating person likely no edit permission: " + error.stack || error);
});;
}
NewPerson();
todo
- find by id
- .save()
- .create()
- .find()
- .findOne()
help and support
you can join my discord server https://discord.gg/RYQbmj7 or email me user (at) invalidlag.com