Using Google Spreadsheets as a Database


#43

I did find a way to save multiple pieces of data to one key, using JSON, which is very useful.


#44

So… Why did you post that here?


#45

It was a reaponse to you saying you’d want multiple values set to one key?


#46

He meant a data size limit on spreadsheet cells. His solution was to save data to different cells in columns.


#47

“I don’t know what the limit is on a single cell of a spreadsheet, I’m going to work on a V2.0 at a later date that will allow you to save multiple pieces of data to one key, and they will be stored across that row.” is what I was responding to


#48

I’ve recently received errors like this:

The “Database error: nil” error seems to happen when I use PostAsync and the big error when I use GetAsync.

Both still work I just get errors which spam my devconsole and break my scripts as the module returns nil but one of my script checks if it’s returned true or false to update data.


#49

I’ve not experienced these myself, but the code is almost 4 years old at this point so changes to Google’s API could be responsible.


At this point I have stopped supporting the use of this module. It's outdated, however I might consider rewriting it if there is genuine demand. Though, I think it better to create a database tutorial.

#50

In fairness, Google Sheets has basically 100% uptime so it is a reliable way to do datastoring.


#52

No, Google Sheets is not a good technology to use for any serious project. It will kick the bucket when you get any kind of significant load because it has limited rates (since it’s not to be used as a database, it’s just for convenience purposes for automating spreadsheets), and it’s dangerous to assume you will never get high concurrency on a platform that is as viral as Roblox. Your game may become popular overnight and completely break because your Google Sheets “database” can’t handle the load.

Best to use dedicated technologies for this. No one uses Google Sheets as a database professionally, really, it’s quite ridiculous.


#53

Google Spreadsheets are used by several small communities that aren’t wishing to appear on the front page and have seperate games which require the same data. For example br_ay’s community has a Google Spreadsheet “database” to save data such as the current wand of the player, the wands a player owns, their coins etc. Br_ay has a community (which will die due to the termination of private modules) which uses his wands and the data gets transferred to every single of the games that are authorized to use them. Another example would be a small group that I develop for where we keep a lot of RP data of the players and we have around 4 seperate games where the data is required to add a namelabel, set their house etc.


#54

See:


#55

Can it be used as a police database system?


#56

Although Google Sheets API has a read and write request limit which is 500 request per 100 seconds per project and 100 requests per 100 seconds per user. First lets see if that is enough for a front pager game. I am not sure what the difference between “user” and “project” is so we will see what amount of requests per 100 seconds is necessary for a well managed database of a front page game.

So to test this lets take the extremes. An extremely well managed database system would mean you need 1 read request and 1 write request per play session. (One read to load the players saved data and one write when they leave to update their data). Then we need to know how often people are leaving and joining, my own front page game had an upper limit of 3000 players, back then that was normal for #2 on the games page, however this means I don’t have developer stats data on a game with more players than that. For my game the average visit length was 8 minutes. If we do some rough math then we can say that at any given time when 40,000 players each one is playing for 8 minutes which is 480 seconds. So the theoretical peak request would be 40,000 requests per 480 seconds 40000/480 = ~84 requests per second. or 8400 requests per 100 seconds which is indeed way above 100 and 500 requests per second. This would however be the absolute peak request, perhaps someone who is good at probability and statistics can calculate the 95th percentile of the needed requests per 100 seconds which will be much lower.

That being said, if you pay them (google) they allow you to increase your request quota but for money.
40,000 players seems to be the upper limit of players in the top games page game at the moment (Jailbreak). If you make a pay-as-you-go deal with google you can make a special deal with them because its also above their maximum set price range of 500,000 requests per month. Which would be 2,678,400 requests per month or perhaps around 1 million if you consider that the game has its peak in one region at 40000 and at night it has much less players. the rate for up to 500,000 requests per month is 0.00227 per request so that comes out to 84*0.00227= 0.19068 per second. So at about 19 cents a second you would be at ~$16,500k per day. Now that is alot. That is about $500,000 a month. (Keep in mind you can negotiate a better deal with google for very large data transfers) however with this rate it might be worth it if you make the maximum DevEx profit of about 1 million USD per month. In that case half of your earnings would go to google. In the case that your game does not make that much, then it is not going to be worth it. Also keep in mind that if your game does indeed have that peak playing amount 24 hours a day then you would potentially be making alot of robux so it may work out for you.

In conclusion I would have to say that indeed IF you want to not pay any money to google it should be only used for small projects. Well then but how small?

100 requests per 100 seconds, playtime of 8 minutes… that means 480 people can join or leave the game every 8 minutes. A game with how many players would have that leave and join rate? If there is X players and they each play 480 seconds long, at any given 100 second interval 100 of them will be leaving or joining. I’m not good at probability so lets look at the extreme case that everyone is joining at once which would mean your game would support 480 players. If you API is being used by a project (500 requests per 100 seconds) still free. That would mean 500 players can join per 100 seconds. so 5 times the previous rate so 5 times the players 4805 = 2400 players. So the free API at peak usage supports about 2400 player games.

For anything else, like on request functions this will work because a denier request can repeated after 100 seconds. You can also program your database system to not crash when the request is denied but wait for the quota of the next 100 seconds to open and then write the data, likewise if a read request is denied you can make the program wait for the next 100 seconds and then re-request.

So in conclusion I would say go ahead and use them, it is very handy especially because you can edit the spreadsheet on the go and change things in-game without actually going in game, BUT know what you are using it for and be aware of its limitations.


#57

Growth is often exponential. Once you get a few hundreds of players of once, the jump towards thousands of players at once is small and can happen at unpredictable times, especially on a platform like Roblox (many cases of games getting to the top places in the sort overnight exceeding the expectations of the developer). I would therefore suggest you never chance it and always pick a reliable solution if you are anywhere above the order of tens of concurrent players, unless you really don’t care about the situation where your game breaks if it blows up somehow in popularity.


#58

It really depends on what you use it for. If you use it to save and load player stats then yeah be prepared for it to break your game once it gets to around 2.4k simultaneous players. (more players if you pay) But if you use it for any other application that will use up to 100 requests or 500 requests per 100 seconds of game-play over all servers then it is a good and very powerful tool to use. These could be a police database

as they requested. So for example only when you arrest someone does it send a write request and logs their arrest in the database. And read requests would happen when someone wants to check the database (which making them wait for the quota to clear up space) would be ok, you would just make is say ‘loading’ until the request comes through. So again roughly this would mean that as long as in your game across all servers is not arresting more than 100 players per 100 seconds (user account) or 500 arrests per 100 seconds (project account) then it would be fine. As long as arresting isn’t a core means of game play for large games this could work, for games such as city games where being a police officer is only one of many available jobs to play. You could also make all the arrests go to the database only at the end of the server when the last player leaves and that would drastically reduce the amount of write requests made.

Another very useful use could be to push variables to all your games and servers from outside of the game. Lets say you want to make a random event like an egg drop happen in all your games and all servers of your games. Then you could, in the spreadsheet, input a variable which your games will see and each server of each of your games would commence an egg drop.

So in the end yes[quote=“buildthomas, post:57, topic:12658”]
your game breaks
[/quote]
if you use it to save player stats and your game becomes popular which most of us here probably want. But there are endless other very handy and scale-able uses for this very handy tool.

For player stats just use roblox datastores, and if you insist on having player data in a google spreadsheet on your 40000 player game then either pay for it of create a little script that forewards all the datastore data to the spreadsheet as one string every now and then and also one that takes the data from the spreadsheet and saves them in the datastore. This could be tricky and the game when it is running might have to check for changes every couple minutes.


#59

Actually, that is a cool idea. Didn’t think of it so I’ll try that on something. I did have problems so hopefully using a Google Spreadsheet would solve that. Clever idea!


#60

I used spreadsheet as a database for a superclan, I swear to god I will never use it again. It does not scale like you think it will and its also the most painful thing to script for with a very poor editor.

Learn how to use a proper database such as MongoDB and MySQL. It will save you so many headaches.

Google spreadsheet is for analytics and forms so please dont treat it like a database.


#61

I’d rather use this for small scale games, as I wouldn’t rely on this resource as it is not tailored for its purpose, and as it is rate limited, it is not taken so lightly as there is a possibility of your game having thousands of players on concurrently, which leads to Google Sheets not being able to store everything on at once.

You should really use dedicated sources rather than using a resource which is not meant for these uses.


#62

A good external resource if you don’t know what you’re doing is to just install CDBT on Wordpress. Can sometimes be a bit tricky to setup, but it’s easier than setting up your own API (obviously if you can do that, then you should do that)