Using Google Spreadsheet In Roblox


  • Google Spreadsheet can be used as an alternative to using a data store with the main advantage of being able to access the data in two separate universes though like the data store it does however have its own limitations which need to be considered before use. Currently it has a request limit of 20,000(Standard user), 100,000(Business use) calls per day which it not a lot for top games in Roblox, so how can this be useful?

    Well the simplest example is that you get full access to the capabilities of a spreadsheet which can be used to aggregate data, format data or simply log data such as error messages.

    Tips

    • To save on calls it is best to send larger amounts of data per call.
    • Using the data store to track the number of calls made per day and even setup your own log system on the spreadsheet itself.
    • All google script changes are logged and multiple scripts are supported.
    • All changes must be uploaded as a new version.

    Requirements

    • A gmail account.
    • Basic knowledge of Java Script.

    Setup

    1. Login to gmail and access Google Spreadsheets
    2. Create a new spreadsheet or access an existing spreadsheet.
    3. Note down the url https://docs.google.com/spreadsheets/d/[Sheet Id]/edit this will be used to access the spreadsheet either by its sheet id or full url.
    4. Goto Tools->Script Editor. A new window will open up allowing you to input the Java Script.

    Google Spreadsheet API

    When creating the script you must include the function names doGet and doPost as a get request will run the doGet function and a Post request will run the doPost function both will be passed the data as an arg for the request, see web app link for more information.

    Roblox And Google Spreadsheet Script Setup

    You can use the following scripts for the basic setup and requests:-

    Roblox Server Script Module
    Or take a copy from Roblox link

    local httpServ = game:GetService('HttpService')
    local urlEnc = httpServ.UrlEncode
    local getAsync = httpServ.GetAsync
    local postAsync = httpServ.PostAsync
    local nxt, str, typOf, pcal = next, tostring, typeof, pcall
    
    -- input validation
    local function chkInput(tbl)
    	if typOf(tbl) ~= 'table' then warn('Argument must be a table') return false end
    	if tbl.runFunction == nil then warn('No run function defined in the table passed') return false end
    	if tbl.getSheetBy then
    		if tbl.getSheetBy ~= 'id' and tbl.getSheetBy ~= 'url' then
    			warn('Incorrect get sheet methods defined, use id or url') return false
    		end
    	else
    		warn('No sheet get method defined') return false
    	end	
    	if tbl.getSheetData == nil then warn('No sheet data passed') return false end
    	return true
    end
    
    -- convert the table of data to the request format
    local function convertData(data)
    	local tmp = ''
    	for i, v in nxt, data do
    		local a, b = str(i), str(v)
    		if a and b then
    			tmp = tmp .. urlEnc(httpServ, a) .. '=' .. urlEnc(httpServ, b) .. '&'
    		end
    	end
    	return tmp:sub(1, -2) -- remove last &
    end
    
    -- make the post or get request
    local function doCall(isGet, tbl, basePath)
    	if isGet then
    		return pcal(getAsync, httpServ, basePath .. '?' .. convertData(tbl))
    	else
    		return pcal(postAsync, httpServ, basePath, convertData(tbl), 2)
    	end
    end
    
    return function(scriptId, isCustom, dev)
    	if typOf(scriptId) ~= 'string' then error('Script Id must be a string') end
    	local basePath = 'https://script.google.com/macros/s/' .. scriptId .. (dev and '/dev' or '/exec')
    	if isCustom then
    		return {
    			post = function(tbl)
    				return doCall(false, tbl, basePath)
    			end,
    			get = function(tbl)
    				return doCall(true, tbl, basePath)
    			end
    		}
    	else
    		return {
    			post = function(tbl)
    				if chkInput(tbl) then
    					return doCall(false, tbl, basePath)
    				end
    			end,
    			get = function(tbl)
    				if chkInput(tbl) then 
    					return doCall(true, tbl, basePath)
    				end
    			end
    		}
    	end
    end
    

    Google Spreadsheet Script
    This script can be altered to run multiple functions within the switch statement.

    function mkResultArray(argList, errorMsg, status)
    {
      var tmp = {};
      tmp.error = errorMsg;
      tmp.requestType = argList.contentLength == -1 ? 'Get' : 'Post';
      tmp.dataPassed = argList.parameter;
      tmp.status = status;
      return ContentService.createTextOutput(JSON.stringify(tmp));
    }
    
    function test(argList, spreadSheet)
    {
      var tmp = {};
      tmp.status = true;
      tmp.requestType = argList.contentLength == -1 ? 'Get' : 'Post';
      tmp.dataPassed = argList.parameter;
      tmp.spreadSheetName = spreadSheet.getSheetName();
      
      var sheets = spreadSheet.getSheets();
      var data = [];
      for (var i = 0; i < sheets.length; i++)
      {
        data[i] = sheets[i].getName();
      }
      tmp.sheets = data;
      return ContentService.createTextOutput(JSON.stringify(tmp));
    }
    
    function processRequest(argList)
    {
      var params = argList.parameter;
      
      if (!params.getSheetBy) 
        return mkResultArray(argList, 'No sheet get method defined', false);
      
      if (!params.getSheetData)
        return mkResultArray(argList, 'No sheet data provided', false);
      
      if (!params.runFunction)
        return mkResultArray(argList, 'No run function provided', false);
      
      try
      {
        var spreadSheet = params.getSheetBy == 'id' ? SpreadsheetApp.openById(params.getSheetData) : SpreadsheetApp.openByUrl(params.getSheetData);
        
        switch (params.runFunction.toLowerCase())
        {
          case 'test':
            return test(params, spreadSheet);
          default: 
            return mkResultArray(argList, 'Run function not found', false);
        }
      }
      catch(e)
      {
        return mkResultArray(argList, e, false);
      }
    }
    
    // This function will be called upon a get request to this script
    function doGet(argList)
    {
      return processRequest(argList);
    }
    
    // This function will be called upon a post request to this script
    function doPost(argList)
    {
      return processRequest(argList);
    }
    

    These scripts are for example purposes only.

    Saving And Running The Script

    When you are happy with your script goto Publish -> Deploy as web app. You may then be asked to allow the script to access the spreadsheet if so allow access. A new window will open asking you to setup who has permission to run the script with the given script id, note down this script id as it is used to run the script.

    Lastly select new under the project drop down list then optionally add a small description of what has changed in the script if this is your first script you will only see the description text box and then change who has access the the app to Anyone, even anonymous else you will not be able to run the script.

    Using the example above you should have the following setup

    -- id are for example only and will not work, 
    -- add your script id as the first paramater
    local newRequest = require(script.Parent:WaitForChild('GoogleSpreadsheetModule'))('AKhyrbw5ab0G-Dy3mabywQkItVRsU2EzscbkT8PeVsXmxHNLV6RpD64', false, true)
    
    local data = {}
    data.getSheetBy = 'id'
    data.getSheetData = '1G_BRb6ffyeZR-aJjrdlD_4pwlB_DrCf343zAjuj-WA' -- the spreadsheet id 
    data.runFunction = 'test'
    	
    print(newRequest.post(data))
    print(newRequest.get(data))
    

    I hope this helps, please comment if there are any changes that need to be made.


  • This is awesome! Totally gonna try this.
    Also, to solve the maxing out problem, maybe make multiple spreadsheets, and just cycle through them until one of them doesn't return an error. I don't know, I've never really used HTTPService.


  • @ChipioIndustries I think the limits are per account. But I do feel it's a little unclear.


  • With the limit, I was thinking you could determine to use a certain spreadsheet between two based on the users' userId. Example, if their userID was an even number you could use one spreadsheet over another to save data. This would just split the data between two spreadsheets, but doing something like that you could maybe use multiple spreadsheets.

Log in to reply
 

Looks like your connection to Scripting Helpers was lost, please wait while we try to reconnect.