Using HttpService with Google Sheets

[Dummiez]

EDIT: Please note this tutorial is quite dated, and may not work as expected. It is also recommended to not use as a database, unless for experimental/testing purposes.

decided to write this up around 3am in the morning, so excuse my weird grammar

So I thought I’d create a thread of an unusual way on how to record pretty much anything that you want to in your game such as players purchasing developer products, script errors, or even the chat history using HttpService.

The coolest thing about this is that it’s free, does not require any website hosting/setting up a database because it uses Google Docs (Google Sheets) so you can easily link your Google account and download, search and make record of whatever happens in-game! I don’t use HttpService currently for any other way and would like to get more into it, but I thought I’d share this if you want simple PostAsync stuff.

Now obviously this kind of use of the HttpService wasn’t exactly meant to be used like this as the submission is the url and not the data string. It’s better off for anything you want to store in forms or cells.

I’ll try to make this as easy as possible to understand for those who don’t really script but would like something like this in their place. Let’s assume you already have something that you want to use this for. Make sure to have the HttpService enabled in your place by going to ROBLOX Studio, click on the Model > Service > HttpService and put a checkmark on the HttpEnabled property. If you already have these properties set then you’re good!

Create a new Script (or better yet if you know how to use ModuleScripts you can go ahead and use that) and place it in the ServerScriptService or wherever fit for your projects.

Inside the script, copy and paste the following code in (or whatever variation you have in mind):

local hs = game:GetService("HttpService")

local hsEntry1 = ""
local hsEntry2 = ""
local hsKey    = ""
local hsForm   = ""
local hsHost   = "https://docs.google.com/"

local function logSheet(var1, var2)
	local hsLink   = hsHost.."forms/d/"..hsForm.."/formResponse?entry."
	                 ..hsEntry1.."="..var1.."&submit=Submit&formkey="
	                 ..hsKey.."&entry."..hsEntry2.."="..var2.."&ifq"
	-- Upload onto Google Sheets!
	-- print(hsLink)
	hs:PostAsync(hsLink, "", 2)
end

Now for the more complicated stuff. Go to the Google Sheets website https://docs.google.com/spreadsheets/ (or create a new one if you don’t yet) and create a new sheet. When a new one is generated for you the spreadsheet url be quite long, however there is a certain group of characters in that url which is the spreadsheet form. You want to copy that and paste it into the hsKey.

Now, in order to be able to post data you will need to create a new form for the spread sheet. It can be done by selecting Tools > Create a form

You will see that there is an Untitled Question with Option 1. You’ll want to change that from a Multiple choice to Text in Question Type when you edit the question. Let’s use the first entry for the player’s name.

Let’s add in a second entry. Let’s record player knockout/wipeouts as an example. Click on the Add Item button to add a new entry to the form and use a Text option.

Now that we have the 2 entries, we can select the send form button at the Confirmation Page section. Now while on the same page select the View live form button at the top.

You should have a submission form with the two entries you wrote. Now the URL of the submission form will have something similar to your spreadsheet, however it is not the same. Copy the alphanumeric characters after the /d/ until /viewform. This will be the hsForm. Next comes getting the submission entry numbers. It’s recommended that you have a browser that you can easily see the page source such as Google Chrome. Ctrl+F and search for ‘entry.’. There should be 2 results. They are your first and second entry keys for the Player Name and Player Killed. The first result will be the first entry and so forth. Be sure to get the numbers directly after entry. until the end of the number.

Once you have the form, key, and entries you can now start logging stuff in Google Sheets!

For an example, with the logSheet function, you can run it in a script and it will display on the Google sheet!

logSheet("Player1", "Player2") -- Can be replaced with any other variable to be manipulated as a string

Also, the Timestamp cell takes the date/time from the ROBLOX server so it may be under PST or CDT time zones. As far as I know, the limits to number of cells on Google Sheets is 2 million (I’m not sure if that’s not enough to cover whatever you’re doing) You can read more about it here: Files you can store in Google Drive - Google Drive Help

If you have any questions or comments about what this is still or want to tell me what I should actually be doing then feel free because I don’t really feel like having to get a server to host data unless you find one that’s free and for the purpose of keeping records then that would be nice.

Basically using Google Sheets as a Database. There’s some other pretty cool things I read about using it here: http://acrl.ala.org/techconnect/?p=4001

[Maelstronomer]

Do you have the time to make a step by step guide for total noobs on how to log script errors using this? Twice I tried following the wiki but it left me confused and with borked data in the end.

[i]Edit: Article referenced: Documentation - Roblox Creator Hub

[Dummiez]

[quote] Do you have the time to make a step by step guide for total noobs on how to log script errors using this? Twice I tried following the wiki but it left me confused and with borked data in the end.

[i]Edit: Article referenced: Documentation - Roblox Creator Hub [/quote]

Using Google Analytics

Decided to post this since when I first tried to use this service on my place the Wiki just didn’t seem to cover it (also req.) How to use the Google Analytics to record the sneaky errors and other bugs that other players may cause when they join your game. So instead of having to ask people to report those bugs for you, why not just look at a more easier solution and look at the errors that come up on GA (or HttpService) doesn’t really matter what you use. There’s not much to configure and do …

[sleitnick]

Very cool! Got it to work!

[Aqualotl]

I have been messing around with Google drive and my website, and I was actually about to try to do something like this. This just saved me a bunch of time. Thanks!

[woot3 (Woot)]

I had a look into attempting this with JSON, but I ran into some problems where the data just wasn’t arriving as it should. Not really sure why.

However, from this I was able to improve what you’ve currently got. Hopefully this will be useful.

local hs = game:GetService("HttpService")

local hsEntries = {} -- This is where you store the entry fields. (In order that the variables will be assigned to them.)
local hsForm   = "" -- This is the form ID
local hsHost   = "https://docs.google.com/"

local hsLink = hsHost.."forms/d/"..hsForm.."/formResponse"

return function (...)
	local variables = {...}
	local urlData = ""
	for i = 1, #hsEntries do
		urlData = urlData .. "&entry." .. hsEntries[i] .. "=" .. hs:UrlEncode(variables[i])
	end
	local url = hsLink .. "?" .. urlData
	print(url)
	hs:PostAsync(url, "", 2)
end

[Dummiez]

[quote] I had a look into attempting this with JSON, but I ran into some problems where the data just wasn’t arriving as it should. Not really sure why.

However, from this I was able to improve what you’ve currently got. Hopefully this will be useful.

[code]
local hs = game:GetService(“HttpService”)

local hsEntries = {} – This is where you store the entry fields. (In order that the variables will be assigned to them.)
local hsForm = “” – This is the form ID
local hsHost = “https://docs.google.com/”

local hsLink = hsHost…“forms/d/”…hsForm…“/formResponse”

return function (…)
local variables = {…}
local urlData = “”
for i = 1, #hsEntries do
urlData = urlData … “&entry.” … hsEntries[i] … “=” … hs:UrlEncode(variables[i])
end
local url = hsLink … “?” … urlData
print(url)
hs:PostAsync(url, “”, 2)
end
[/code] [/quote]

You seem to be missing the spreadsheet Id and rather you only have the form Id. Also if you plan on adding entries of more than 2 it is possible however I haven’t come to figure out a relatively easy way on how to add multiple submissions. The issue with this is that your number of entries should be a fixed number (You’ll need whatever number of key entries as well). Be sure not to make them all a required question if you still plan on having blank submission entries.

[woot3 (Woot)]

You seem to be missing the spreadsheet Id and rather you only have the form Id. Also if you plan on adding entries of more than 2 it is possible however I haven’t come to figure out a relatively easy way on how to add multiple submissions. The issue with this is that your number of entries should be a fixed number (You’ll need whatever number of key entries as well). Be sure not to make them all a required question if you still plan on having blank submission entries.

My code runs fine. You do not need to include the spreadsheet ID since the form is already setup to post data to the given spreadsheet. You’ll also notice my code only sends the amount of variables that you include IDs for in the hsEntries table.

You’ll see this is the case by watching what the google version actually is posting when you click the submit button.

[Dummiez]

You seem to be missing the spreadsheet Id and rather you only have the form Id. Also if you plan on adding entries of more than 2 it is possible however I haven’t come to figure out a relatively easy way on how to add multiple submissions. The issue with this is that your number of entries should be a fixed number (You’ll need whatever number of key entries as well). Be sure not to make them all a required question if you still plan on having blank submission entries.

My code runs fine. You do not need to include the spreadsheet ID since the form is already setup to post data to the given spreadsheet. You’ll also notice my code only sends the amount of variables that you include IDs for in the hsEntries table.

You’ll see this is the case by watching what the google version actually is posting when you click the submit button.[/quote]

However using your url layout will not work because there is no verification, which is why you are receiving this error:

The point of the spreadsheet Id being there is to verify that you are the legitimate owner of the spreadsheet and that no one else can simply take the form Id and start spamming your spreadsheet with useless entries.

[woot3 (Woot)]

I don’t receive any error whatsoever. Also there is a simpler way to get the Entry parameters, you can go onto the form you create and go Responses > Get Prefilled URL, generate one and it’ll return all entry IDs.

I should also add you only actually need to use one Spreadsheet, which you can then link multiple forms to.

[Dummiez]

[quote] I don’t receive any error whatsoever. Also there is a simpler way to get the Entry parameters, you can go onto the form you create and go Responses > Get Prefilled URL, generate one and it’ll return all entry IDs.

I should also add you only actually need to use one Spreadsheet, which you can then link multiple forms to. [/quote]

Seem to have misread the JSON error, everything seems fine with what you have. Linking forms is another thing I forgot to mention.

[GuestCapone]

How do you get the hsKey?

[GuestCapone]

Thank you so much! Now I can finally log those pesky mob families in my places.

[size=1]Blackmailing time. [/size]

[CeaselessQuokka]

Out of curiosity, how do you figure this out? I mean just HttpService in general, how do you figure out how to get certain elements from the website?

[Garnold]

This is really neat. A while back I made a module to read from a Google Sheets document; I never got around to uploading data to one though.

[sleitnick]

Okay, so I decided to create a ModuleScript for this that shortcuts a lot of the stuff. All you need is the Form ID and the Key ID parts. From there, you just submit based on the Label name and the Input. I haven’t tried anything other than Text input for now. Super easy.

Sample code:

local googleSpreadsheets = require(blah.GoogleSpreadsheets)
local mySpreadsheet = googleSpreadsheets:Get(KEY_ID, FORM_ID)
mySpreadsheet:Submit({Label = "LABEL_NAME", Value = "VALUE"}, ...)

Demo video of me submitting some data with it:

http://youtu.be/sc0UQUggpVQ

[MrNicNac (nox7)]

This is great and all, but what is the down-to-efficient point? SQL databases have way more durability, can be queried faster and in multiple ways, and can be exported into spreadsheets for viewability purposes.

May be cool, but I feel SQL database management is much more… modern…

[Dummiez]

[quote] This is great and all, but what is the down-to-efficient point? SQL databases have way more durability, can be queried faster and in multiple ways, and can be exported into spreadsheets for viewability purposes.

May be cool, but I feel SQL database management is much more… modern… [/quote]

The point is that it serves it’s purpose of storing data, doesn’t need to be exported because it already is there for you to view and can retrieve data from it. I don’t know what you mean by durability, but this has also zero downtime and no need to worry for SQL injection. Also it requires little to no knowledge of scripting and can be done almost instantaneous.

[MrNicNac (nox7)]

[quote] This is great and all, but what is the down-to-efficient point? SQL databases have way more durability, can be queried faster and in multiple ways, and can be exported into spreadsheets for viewability purposes.

May be cool, but I feel SQL database management is much more… modern… [/quote]

The point is that it serves it’s purpose of storing data, doesn’t need to be exported because it already is there for you to view and can retrieve data from it. I don’t know what you mean by durability, but this has also zero downtime and no need to worry for SQL injection. Also it requires little to no knowledge of scripting and can be done almost instantaneous.[/quote]

Fair enough, but if you are even worrying about SQL injection… then you might be 8 years behind modern web standards.

[Den_S]

[quote] This is great and all, but what is the down-to-efficient point? SQL databases have way more durability, can be queried faster and in multiple ways, and can be exported into spreadsheets for viewability purposes.

May be cool, but I feel SQL database management is much more… modern… [/quote]
Although some proper database is obviously the better choice, it often requires you to pay for a server or some service reliably hosting your database, while this (although extremely hacky) method is free and ran by Google, so it’s fairly reliable.
That’s certainly a valid reason to go with this, if you don’t own a server, in my opinion.

Just do note that since this is not an actual API call for their forms, it can break at any time. Thus it’s actually not reliable at all and I wouldn’t recommend using it :P.