Using Google Spreadsheets as a Database


#1

Overview

A day ago there was a very insightful post about how to use forms to post data to a spreadsheet hosted on a Google drive. I wanted to have a further look into it and managed to create a system that allows you to use one as a database.

How to set one up

Hopefully this will keep things fairly straight forward and easy enough to do.

Step #1: Create a new spreadsheet

Open up google sheets and create a new spreadsheet.

Step #2: Set column names

Now what we need to do is setup the spreadsheet. The database is set up to work like ROBLOX’s using a key and a value.
So what you’ll need to do is change the value of cell A1 to “key” and the value of cell B1 to “value” (these are case sensitive).

Step #3: Freeze the columns

Now we want to freeze the columns so they don’t disappear when we scroll down through our database. Do this by clicking View > Freeze Rows > Freeze 1 Row.

It should now look like this

Step #4: Setup sheets

Each sheet in the spreadsheet is a different database where the sheets title is the id of the database. Later on you can add more sheets but now now we’ll just rename the default one. (You’ll need to add the key and value headers on new sheets).

Rename your sheet to “Global”

Step #5: Save

You can name the file whatever you want, since I am assuming you know how to save you won’t be getting a picture.

Step #6: Create a new macro (script)

For this database to work we need to setup a macro, this probably sounds daunting at first but is much easier than it seems. To do this click Tools > Script Editor

This should open a new window that looks like this

Step #7: Setup said macro

Now we’re going to set it up. First, replace the current code in the open window with the code found here.

Once you’ve done this click File > Save and name it “Database Macro” or anything you feel is suitable.

Step #8: Authorize said macro

Our macro will need to be allowed to read the spreadsheet, this is nice and simple to do. Once you’ve saved the file click Run > setup

Continue

Accept

Step #9: Publish the macro and get the key

This is about the time you want to start considering getting ROBLOX studio open. You’ll also be needing this module.

Now we need to publish our macro, click Publish > Deploy as web app …

You’ll see a new window, project version can be anything that you deem suitable, I chose “First Edition”. Click save new version… You’ll then want to execute the app as “me” and set the access to anyone including anonymous.

You’ll then be shown a URL. Copy it to your clipboard and make sure not to lose it.

Step #10: Setup the module

We’ve done everything we’ve needed to do in regards to setting up the datasheet and the macro, now it’s time to setup the module.
Insert the module into your place and open it up. At the top of the module you’ll see a variable labelled called scriptId, set this as the id of the script (that’s the URL without “https://script.google.com/macros/s/” at the start and “/exec” at the end. Alternatively, if you find it easier you can just change the url variable to that of the url given to you from the webApp. Make sure NOBODY gets their hands on your scriptId, it can be used to change values within the database.

That’s it. Now we can use our module as our database.

Usage

The module works very similar to that as the Datastore service. Here’s an example made using it.

databaseService = require(script.DatabaseService)
local globalDatabase = databaseService:GetDatabase("Global")

globalDatabase:PostAsync("Cool People", "RbxDev")
--print(globalDatabase:GetAsync("CoolPeople")) -- Legacy typo ftw
print(globalDatabase:GetAsync("Cool People"))

>> RbxDev

Warning: Do not post anything with the key ‘key’, this will rename your first column breaking the database. I was too lazy to implement anything to stop people doing that. You’re smart people anyway. Don’t do it.

Limitations

The only real limitations I have come across are the ones imposed by Google, you will find them here.

Applications

This has some practical uses, one example is that you can edit the database from your phone or browser, so if a player hasn’t been awarded the points they bought for some reason, you’ll be able to fix this manually. It also allows you to share data between different games, which may come in useful if you want to reward players who play more than one of your games.

One final thing to also add on is that you can download your entire database as a spreadsheet, so you can easily back it up if you need to.


Data Saving Across Places
Question about Google Spreadsheet Databases
Methods for realtime cross-server data transfer?
Is it possible to check when did a player acquired a badge from my game?
Trying to save leaderstats with httpservice
Is there a way to read all of the values from a datastore?
Best way to create a Global Marketplace?
DataStore + Scoreboard
HTTP Error: 405
HTTP Error: 405
Using HttpService with Google Sheets
#2

[strike]Should put this in tutorials, but this is awesome![/strike]
Looks like it was moved after I said that


#3

Personally, I would prefer a web service doing stuff like this because then they can set up API keys to stop people adding random data. That’s why I think that you should be using https://developers.google.com/google-apps/spreadsheets/ instead, and not the hacky form-style way of doing it.

Of course, it is the developer’s opinion in the end if they want to do it or not. It’s just my opinion to stay away from such things.


#4

If you’d have fully read the post you would have seen that this is indeed using the spreadsheets API already.


#5

If you’d have fully read the post you would have seen that this is indeed using the spreadsheets API already.[/quote]

Oh, that’s cool.

I was mostly commenting on the original post (that is, the one linked in OP) because I knew that used Forms.

Sorry if I came off as stupid :stuck_out_tongue:


#6

If you’d have fully read the post you would have seen that this is indeed using the spreadsheets API already.[/quote]

Oh, that’s cool.

I was mostly commenting on the original post (that is, the one linked in OP) because I knew that used Forms.

Sorry if I came off as stupid :P[/quote]
It’s cool, my response was a little blunt. Glad you like it though.


#7

[quote] [strike]Should put this in tutorials, but this is awesome![/strike]
Looks like it was moved after I said that [/quote]

I didn’t put this in tutorials for a reason. I was trying to show it off, but also provided a way to do it yourself. The other thing was there was talk about making Tutorials public. I don’t want this thread public, but instead to stay on the dev forums.

Can an admin move this back?


#8

[quote] [strike]Should put this in tutorials, but this is awesome![/strike]
Looks like it was moved after I said that [/quote]

I didn’t put this in tutorials for a reason. I was trying to show it off, but also provided a way to do it yourself. The other thing was there was talk about making Tutorials public. I don’t want this thread public, but instead to stay on the dev forums.

Can an admin move this back?[/quote]

I’ll move it. Just FYI, we haven’t decided about making the category public. Not guaranteed to happen.


#9

So, could you use this to store more detailed data than what is currently done with the Roblox stores and possibly manipulate said data through the spreadsheet macros? If so, that’s awesome!


#10

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.


#11

This is GOLD!

Thank you very much woot3! This will come in handy in ALL my games! And the applications are so wide! Not just error handling!

It’s these kind of codes that makes me happy =)


#12

This is magnificent, woot3!! Thank you for this!


#13

Edit (2018): Several people have been asking me for help with this over the past 2 months. This post was made in 2016, and I haven’t looked at it since. I am a bad person to turn to for help with this.

I am working on a new system for Innovation Inc for promotions, and this is amazingly useful.
I did a lot of tinkering with the JavaScript code (which I am not super familiar with JavaScript), and I was able to get it to accept more than just “value”, and a few other functions I won’t say for now.


The sheet looks a bit different after adding more values and such, but this thread was useful.


#14

Is there any way this can be made public? Somebody in the Roblox Helpers discord wants to read it.


#15

You can get Brad_Sharp to create an image of the thread and upload it to imgur. I have been doing it for my other tutorials.


#16

While this is pretty clever I’m not entirely sure how well it will scale. I’m 100% certain that Google has throttling on their spreadsheets so if your game gets really popular, this is going to break.


#17

Limits can be found here.
Under “URL Fetch calls”, the limit is 20,000 requests per day. A considerable number but a game at 5,000 concurrent players with constant saves will easily reach that limit.
Innovation Security does use this still for logs, and it is fine as long as we don’t do 20,000 commands in a server a day, where the server may crash from having 20,000+ entries in a table


#18

Excellent. Always know the limitations of the technology you’re relying on.


#19

Looking back on the system, I would have added an automatic clearing function. The main limit to my technology is loading something that massive (I can’t load it to clear it, with the output being many hundreds of thousands of lines).


#20

And always learn from your previous efforts :slight_smile: