SheetValues - Using Google Sheets for live updating Values

SheetValues

by boatbomber


I saw a few people asking what open-source values libraries exist for Roblox for things like fflags, and most of them relied on small providers. I prefer to use Google’s services, since they’re big and reliable. So I wrote this.

Using Google Sheets allows you to update your values from your phone, desktop, or tablet. It’s supported on all devices which makes it a really good “console” for live value editing.

This system updates every 30 seconds (and that number is configurable within the module). This allows you to propagate changes to all your servers really fast. Only one server actually calls the API, the rest get it through MessagingService or DatastoreService. This keeps your HttpService usage down to a minimum, and keeps Google from being annoyed at us.


Setup:

Getting started is really easy. Gone are the days of API keys and custom sheet macro scripts.
All you need is the share link, set to “Anyone on the internet with this link can view”. Copy that link.

The link will look something like this:

docs.google .com/spreadsheets/d/ALPHANUMERIC_SPREAD_ID/edit?usp=sharing

Copy the big spread id out of that link, as that’s how our system will know what spread to read from.

If you’re using multiple sheets in a single spread, the SheetId will be at the end of the main url. Look for “#gid=” and copy everything after the equals symbol. This is really useful for having one spread per game, with multiple sheets in it for your various systems.
image

docs.google .com/spreadsheets/d/ALPHANUMERIC_SPREAD_ID/edit#gid=NUMERIC_SHEET_ID

Pass that into SheetValues.new("ALPHANUMERIC_SPREAD_ID", "NUMERIC_SHEET_ID") and it will return a SheetManager linked to that sheet. Note that the SheetId parameter is optional and will default to the first (or only) sheet in your spread.

What should your Google Sheet look like?
Well, rows are turned into Values with each column entry being a Property of the Value.

Here’s the structure:

The first row of the Sheet is the Header. This row will NOT become a Value, rather it defines how we parse the subsequent rows into Values. Each entry into row 1 becomes the key for that column (Property).

Example:

Name          PropertyName     AnotherProp
TestValue     100              true
NextValue     300              false

This results in two Values being stored and structured like so:

SheetManager.Values = {
    ["TestValue"] = {
        PropertyName = 100,
        AnotherProp = true
    },
    ["NextValue"] = {
        PropertyName = 300,
        AnotherProp = false
    },
}

It’s not strictly enforced, but it is STRONGLY recommended that you have a “Name” Property so that it will index your values by Name (will use row number if no Name prop exists), as it is much easier to for you to work with.

If you have a boolean or number entered, it will attempt to convert the string into your intended datatype.
To create special types, you can explicitly mark them by having the property be “Type(val)”, like “Vector3(1,0,3)”

Supported explicit property Types (not case sensitive):

  • string (for ensuring a number/boolean remains a string)
  • array
  • dictionary
  • Vector3
  • Vector2
  • UDim2
  • UDim
  • Color3 (0-1)
  • RGB (0-255)
  • BrickColor
  • CFrame
  • Enum
  • Rect


API:

function SheetValues.new(SpreadId: string, SheetId: string?)
returns a new SheetManager

function SheetManager:UpdateValues()
gets the latest values of the sheet
(This is called automatically and is only exposed for critical cases)

function SheetManager:GetValue(ValueName: string, DefaultValue: any)
returns the Value or DefaultValue if the Value doesn’t exist
(This is the same as doing "SheetManager.Values.ValueName or DefaultValue" and only exists for style purposes)

function SheetManager:GetValueChangedSignal(ValueName: string)
returns a RBXScriptSignal that fires when the given Value changes, passing two arguements in the fired event (NewValue, OldValue)

function SheetManager:Destroy()
cleans up the SheetManager

table SheetManager.Values
dictionary of your values

number SheetManager.LastUpdated
Unix timestamp of the last time SheetManager.Values was updated

string SheetManager.LastSource
Name of the service used to retrieve the current SheetManager.Values (Google API, Datastore, Datastore Override, MsgService Subscription)
(Used for debugging)

RBXScriptSignal SheetManager.Changed(NewValues: table)
Fires when SheetManager.Values is changed


Example:

A good use of these live updating values is developing a anticheat system.
You can create Values with properties like PunishmentsEnabled so that you can test various methods and thresholds without punishing false positives while you work.
Additionally, you can add properties to the Values for thresholds and cheat parameters, so you can fine tune your system without needing to restart the game servers, allowing you to gather analytics and polish your system with ease.

Sheet used by the Example Code:

Name                        PunishmentEnabled      Threshold
SpeedCheat                  FALSE                  35
local SheetValues = require(script.SheetValues)
local AnticheatSheet = SheetValues.new("SPREADSHEET_ID")

local function PunishCheater(Player)
    if not AnticheatSheet.Values.SpeedCheat.PunishmentEnabled then
        -- Punishments aren't enabled, don't punish
        return
    end

    Player:Kick("Cheating")
end

local function CheckSpeedCheat(Player)
    if Speeds[Player] > AnticheatSheet.Values.SpeedCheat.Threshold then
        SendAnalytics("SpeedTriggered", Speeds[Player])
        PunishCheater(Player)
    end
end

Source:


16 Likes

Bravo! This module is fantastic. One feature that I would appreciate is support for multiple pages. I.e I could have a FFlag page, but also a Codes page. Maybe just add an optional argument to SheetValue.new()?

Realistically you could achieve the same effect by just using two different spreadsheets and constructing two SheetValue objects, however I prefer having pages inside of my Sheets to keep everything in one place.

2 Likes

Update: Improved Events

  • Removed SheetManager.Updated
    This event was basically worthless as it just fired every X seconds

  • Added SheetManager.Changed
    Fires when the .Values table is changed

  • Added SheetManager:GetValueChangedSignal(ValueName: string)
    Returns a RBXScriptSignal that is fired when the given Value changes, and the event fires with (NewValue, OldValue)

2 Likes

Update: Support for multiple sheets in a single spread

This update is courtesy of @MrAsync. Thank you for the PR! Contributions are always welcome.

Some of you use multiple sheets in a single spread, like this:
image

Prior to this update you would only be able to create a SheetManager for Sheet1. Now, there’s an optional second parameter to SheetValues.new() that allows you to link it to a specific sheet so you can have a SheetManager for “some other sheet”.
Instructions on how to do that have been added to the Setup section in OP.

This is really useful for having one spread per game, with multiple sheets in it for your various systems.

Critical Fix to multiple Sheet Support!

An embarrassingly large oversight- multiple sheets would use the same SpreadId as their key for the Datastore and MsgService stuff, leading to them overwriting each other and causing a huge mess.
The key is now generated by combining the SpreadId and SheetId so no sheets will collide like this anymore. To keep the key within the limit for datastore and msgservice, it gets put through a quick SHA1 hash.

Critical Fix

So, I noticed that sometimes Google would simply return an incorrect CSV. I couldn’t figure out why. Eventually, I figured out that Google does this thing where it tries to guess what rows are “header” rows and merges them. However, it’s dumb as a brick and got it wrong every time you had a non number or non boolean value on Row 2.
It took me a while but I found out that you can explicitly tell Google the header size in the URL so it stops ruining your CSV with faulty merges. So that’s what this fix is!

Major Update

V2.0 is here!

Rather than have a type column to apply to a property column, the type should be written/inferred within the Value column itself.
This removes extraneous columns, and allows easier and cleaner sheet writing.

Once we have this ability, the idea of a strictly enforced sheet structure becomes a hampering relic. It isn’t needed anymore, since values don’t need another column for their type. Therefore, this version allows you to create any number of arbitrary properties per Value.

I have updated the docs in OP that express the new behavior and structures.

6 Likes

Patch Update

V2.01

Released a bunch of important fixes:

  • Fix MsgService being fired when it shouldn’t be
  • Added support for CSVs over 1KB in size
  • Fix critical error caused by self in improper place
  • Fix improper datastore failure handling
2 Likes