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
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.
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.
The only real limitations I have come across are the ones imposed by Google, you will find them here.
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.