Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

[RELEASED] Google Sheets For Unity

Discussion in 'Assets and Asset Store' started by Novack, Jan 19, 2019.

  1. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844

    Google Sheets For Unity allows to interact with Google Drive environment, at runtime or design time.

    This unlock a world of posibilities: virtually free backend, for collaboration in design and development. Customize your workflow with flexible and solid well known solutions!

    With more than 5 years on the Unity Asset Store, an asset which is the result and product of actual field work, and with the accumulated experience of having thousands of users, it represents a classic and default choice for simply and efficiently connect Unity and Google Spreadsheets.

    Check the online demo!

    Head to the asset page, and check the WebGL demo for yourself!
    Set a spreadsheet id on the input field and check how the spreadsheet is filled with content, updated, and you can change content on the sheet and check how the demo will retrieve the fresh data upon request.

    Highlights:

    ✔ Read & Write to spreadhseets (runtime and design time).
    ✔ Upload & Download files: binary, text, images.
    ✔ Works with desktop, mobile and WebGL.
    ✔ Source code included.
    ✔ No Google account credentials required on the Unity side.
    ✔ Works with private and public Google spreadsheets.
    ✔ Free SKDs dependencies: no Google Data API required, standard Unity UnityWebRequest calls.

    Multiple barebone examples included:
    • Spreadsheets basic use.
    • Files: loading and saving from both, disk and Google Drive.
    • Images: load from disk or screenshot, save and retrieve from Google Drive.
    • Simple localization example.
    • In-Editor use demo.
    • Unity Console Logs to cloud example.
    • Session context: use a custom player login system.

    How it works?

    The Unity side API enables working with spreadsheets as object tables, or just cell by cell. Creating and retrieving folders, text and binary files. Saving and loading png or jpg images files.

    This client connection on Unity side provides practical queries that will cover most basic interactions. It connects to a webservice which is deployed on the developers Google Drive account. The source code for both, the Unity API and the webservice are provided, enabling tweaks on the available queries, or full customization and adding new posibilities!

    Documentation


    Unity API docs are public and available online here.
    Complete developer guide will be available with the asset in PDF format.

    Disclaimmer

    This package requires intermediate to advanced programming skills in order to understand and use the asset for your own purposes.

    Note on the "Lite" Edition

    The original version has been rebranded "Lite" edition, to denote the difference with the new asset (described on this thread). This new main version, encompasses wider use cases, including and extending capabilities that used to be scattered among 3 or 4 other assets (siblings from GSFU, that I named "CloudTools").

    In essence, the Lite version focuses exclusively on Google Spreadsheets, while the main version goes a bit beyond.

    Status


    Released!

     
    Last edited: Oct 1, 2019
    LaurieAnnis and rom317 like this.
  2. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    LaurieAnnis and rom317 like this.
  3. yoslan

    yoslan

    Joined:
    Oct 3, 2012
    Posts:
    2
    Is it possible with this asset/package to load the data of a specific pie chart from a specific google sheet?
     
  4. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @ccuser, I would say yes, in the sense that the data is on the sheet, while a chart is usually a mere presentation of that data. So while the asset doesnt interact with charts on google spreadsheets, do handle data on the sheet themselves. Hope it helps.
     
  5. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Update: Tomorrow is release day :)
    Only some last minute revisions and tests are pending, will keep you posted!
     
  6. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    zyzyx likes this.
  7. EdgarasArt

    EdgarasArt

    Joined:
    May 21, 2015
    Posts:
    11
  8. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @EdgarasArt the one on itchi.io is the main version, at the bottom of the page you'll found the buy button. The Lite edition is on Unity Asset Store only.
     
    Last edited: Feb 1, 2019
  9. Xelnath

    Xelnath

    Joined:
    Jan 31, 2015
    Posts:
    402
    Hey, do you know why Enums don't get deserialized when I pull them back down from the google sheet?
     
  10. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @Xelnath, is related to UnityEngine.JsonUtility, it does not handle Enums, you'll have to manually parse from string to enum in a post process (or int to enum, depending how you serialized it).

    Now that you mention it, I will add a method to the JsonHelper class to make this operation simpler, but you'll still need to do a post-process using it. Taking notes for next patch!
     
  11. Xelnath

    Xelnath

    Joined:
    Jan 31, 2015
    Posts:
    402
    Okay. Would I be better off using a different json deserializer then?
     
  12. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    That is also a good option, yes, although it depends highly on your specific needs, in terms of measuring simplicity and maintenance burden, againts small patches like implementing manual deserialization post process methods for each required class.

    In all honestly, Im using it because for the basic use cases there are no needs to introduce extra dependencies, but JsonUtility is fairly limited and limiting, and Im anxiously waiting for some improvements mentioned by Unity staff.
     
  13. Xelnath

    Xelnath

    Joined:
    Jan 31, 2015
    Posts:
    402
    If anyone has a suggestion, it would be greatly appreciated :)
     
  14. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    @Xelnath in my road to minimalism, before stoping on Unitys JsonUtility I used Newtonsoft Json.NET and LitJson. I can speak good of both, specially about the later, given my intent to go after the smallest possible.
     
  15. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Did you know...? (Tip)

    Is well known (and evident) that Google sheets for Unity works with private and public spreadsheets from the Google Drive account owner.

    But, it also works with any spreadsheet, no matter the google account, if the sheet is public! This can be seen on the online demo, where you can provide your own spreasdheet and the example will let you work on it!

    Head to the asset page, and check the WebGL demo for yourself.
     
    Last edited: Feb 13, 2019
  16. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Did you know...? (Tip)

    A leftover from the research required to circunvent cross-origin resource sharing or CORS (which was necessary for making the asset work on WebGL), is an undocumented feature: you can have server responses in JSONP.

    If for some reason you need the server to answer in JSONP, all you need to do, is add a "callback" field to the POST form (with any content), and the server will return in the mentioned format :)
     
    Last edited: Feb 19, 2019
  17. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    Sorry if you've answered this before, can this asset serve as a data base in a multiplayer scenario or is will it only work for in house development needs (in other words, if I create a google drive folder and store image assets I want players to be able to access alongside a spreadsheet containing data, if this folder is made public, would players be able to play the game and access the data / images seamlessly?)? Secondly, will it only work with uploading or downloading .png and .jpg files? What about .pdf files?
     
  18. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @DADA_universe,

    The asset can be used in production for sure. But of course it highly depends on the project and the scope. To put it in dumb terms, is not the ideal for an MMO :) But if the game or app requirements can handle stateless requests (not persistent sessions), and doesnt need a lot of queries to the server, then you have a good case and a nice perk by having your own backend. If you start having congestion you can simply add a second instance of the web service accessing the same data, and make the game poke different urls.

    Also, a good point in favour is that if at some point having the database and the service as this becomes unmanageable, the data is on spreadsheets, so portability is not an issue.

    The folder doesn't need to be public ;)
    The only reason to use public sharing is on the cases where you would want to access third party spreadhseets. On that case would need to be shared by the owner indeed, but if you're talking of your own data, on the same Drive account, it does not need to be shared at all.

    It depends on what you mean by seamlessly. You'll be of course requesting data from a remote location, so there will be an expected time to retrieve it. Other than that, yes, players would be able to get the data.

    The new API lets you upload and download any file type you want, text or binary, so PDF is covered.

    Edit: the public api docs are available online here.

    Thanks, hope it helps!
     
    Last edited: Feb 13, 2019
  19. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    Thanks, that does seem reassuring. To be specific, I'm creating a virtual exhibition experience - just a first person camera / player navigating past and interacting with a set of exhibition screens. I have images, videos, audio files and pdfs to work with and I need a way to store and load these, in a way that the admin that run the project can curate the experience and change it from time to time, hence, Google drive was always a preferred option, but I was not sure if it was fine for production. This gives a bit more comfort and if it works, I would be building even more experiences on the back of this. Is there anything you think I should look out for when adopting the asset for this use case? For example, when you say 'lots of queries to the server', what would you say is the threshold in terms of traffic, beyond which the experience would degrade? That it supports WebGL builds is a plus, I already have a multiplayer set up using WebGL and a NodeJS server where data is being sent in JSON format, using Unity's JsonUtility, which is also a plus seeing you have focused on Unity's JsonUtility as well. I'm looking through the docs meanwhile and am just about ready to upgrade to GSFU.
     
  20. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Oh when I say lots of queries I mean Google Sheets For Unity works over http(s) protocol, is not a persistent stateful connection, so if your clients need constant communication, that would mean a lot of separated requests to handle, and on that case it would unlikely be a practical escenario to use the asset (this does not seem to be the case for your project).

    I think the case you describe is good. The service should not degrade with user concurrency, but you may hit some Google webapps limits, on which case you can provide with further deployments of the webservice to improve the situation, just have that capability into account beforehand on your app.
     
    Last edited: Feb 13, 2019
  21. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    Okay, thanks for this, I'll get cracking. If I hit a snag, I'll come calling. Thanks, you're most helpful.
     
    Novack likes this.
  22. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    How to keep DriveConnection alive between scene loads? Adding a new DriveConnection prefab to each scene crashes, and adding below fixed the problem but not sure this is right approach?

    public class DriveConnection : MonoBehaviour
    {
    public ConnectionData connectionData;
    public static DriveConnection driveConnnection;

    void Awake()
    {
    if (driveConnnection != null)
    GameObject.Destroy(driveConnnection);
    else
    driveConnnection = this;

    DontDestroyOnLoad(this);
    }
     
  23. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @SydInt

    Is up to you to decide the right way to do that. On the previous version I used to have a singleton as basic implementation, but singletons more often than not introduce new problems, and I didn't want people to believe those problems are inherent to Google Sheets for Unity, when in fact the right approach is specific to each project needs.

    In practical terms there are a few things you can do, the simplest of which is DontDestroyOnLoad().

    That being said, adding a new DriveConnection prefab to each scene should not introduce issues, I will check it to make sure its not a bug on the asset.
     
    SydInt likes this.
  24. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    DontDestroyOnLoad() seems to work fine - had to put DriveConnection in a splash screen to prevent duplicates instantiating in new scenes.
     
    Novack likes this.
  25. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Great to know! Thanks for reporting back.
     
  26. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Would you be able to add video filetype support in the next version? I've added below "video/mp4" mimetype to Drive class for now but would be nice to have a corresponding QueryType i.e. QueryType.createVideoFile for handling responses.

    public static void CreateVideoFile(byte[] fileData, string name, string folderName = null, string folderId = null, bool runtime = true)
    {
    Dictionary<string, string> form = new Dictionary<string, string>();
    form.Add("action", QueryType.createBinaryFile.ToString());


    form.Add("mimetype", "video/mp4");
    form.Add("fileData", System.Convert.ToBase64String(fileData));
    form.Add("name", name + ".mp4");


    if (folderId != null)
    form.Add("folderId", folderId);
    if (folderName != null)
    form.Add("folderName", folderName);

    CreateRequest(form, runtime);
    }
     
  27. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @SydInt, glad to see that you're making good use of GSFU :)

    Well the mime types are actually not needed (Im planning to remove them next update), Im encoding all as string and decoding on server using the query type as reference, not the mime types, so you can ignore that. Do you need a separate response handling for videos?

    Once more, is good that you bring this up, because I was planning to trim some things out fore next update, in order to simplify some stuff I see as redundant. Essentially, I looking into having only binary and text file options, and parametrize those two more, regarding type and extension.

    So the idea is that you would have one or two methods on the API to handle files, and then options like: binary, text, extension and format (ie, jpg vs png) would probably be parameters on the method(s).

    What do you think about that? Im looking for simplicity, but still not sure which way I'll take, individual methods for each option, or one method abstracting all options. The abstraction is probably adding complexity before hand, so maybe by trimming things down I would be actually increasing complexity...
     
  28. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    My instinct says not to increase complexity. Keep it simple. Abstraction is great for more elegant code and all but I vote for simplicity and more readable code so your users know exactly what's going on with the code. I reckon people are trying to build around Google Drive using GSFU to keep things simple.
     
    Novack likes this.
  29. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    Feature request: I've seen no evidence that this is included at the moment, but I imagine it would be helpful to be able to create / periodically update a spreadsheet (from Unity at run time), in Google Drive, which lists all the content of a specified Drive folder, with whatever metadata (date, name, size, etc), so that such a spreadsheet can then be queried during game play in whatever way is required and the files can then be manipulated as per requirements. I'm cobbling such a functionality together by creating a GoogleApp Script that can list the contents of folders and then using the available functions in the GSFU api to do the rest, but it strikes me it would make a useful addition to the API.

    Potentially, this could afford this sort of use case for GSFU users, without having them poke into GoogleApp Script code. A journal or notebook where one scene (with the appropriate user authentication) allows the journal owner to upload images or downloadable files, with textual descriptions, automatically populating the spreadsheet in Drive as this happens; and another scene where other players can query and display that content (in the back end, it's the spreadsheet that would be queried) in different ways. Multiple users setting up journals would lead to a unique folder containing a unique spreadsheet being created for each user name, which helps determine which folder / spreadsheet the players interact with at run time.
     
    Novack likes this.
  30. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @DADA_universe, thanks a ton for the feedback!

    Regarding abstraction and complexity, I reckon there is a point there. In fact I already took that simplicity route with the first version, but as some features tends to grow, I fear having so many redundant entry points on the API for stuff that does nearly the same. Still, I agree on going simple and avoid unnecessary abstraction, so I'll try to stick with that. I'll take the challenge to keep some degree of elegancy within those margings :)

    About the feature request, is funny because I actually thought about some sort of "GetFolder" but I was unsure about what it should return. I think if such a function would give a list of file ids and meta datas, would be a fine approach, then you can do whatever is required with it.

    Also, there is a kind of deployment for Google scripts called Trigger, which enable you to automatically run the script at certain periods or hours, however the structure must change a bit, and in order to keep it parametrized you'll have to use script properties or using a hardcoded spreadsheet id just for settings. Have a look at it, may help for the case you was describing.
     
    Last edited: Feb 25, 2019
    SydInt and DADA_universe like this.
  31. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Hey @Novack is there a way to receive notifications when you've updated the plugin?
     
    Novack likes this.
  32. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    @SydInt I thought about the CreateVideoFile() entry point, but realized its not really necessary. I have added the CreateImageFile() because I actually do the enconding to image format there (the dev provides a texture), but in the example you showed, you're already providing the file data. So all you need to do, is call CreateBinary file with the data and the desired extension.

    The puzzled situation its my fault: the mime type is actually *not needed*, is a leftover from previous work that I decided leave there in anticipation of needing it at some point in future, but now its only introducing confusion, and I will remove the mime type from the forms in next update.
     
  33. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    @SydInt regarding the notifications, yes absolutely, itchio provides tools for sending news and updates to customers. So whenever I release the update to the asset, I will send the notifications. And of course will make it public here, and on social media.
     
  34. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Hi @Novack would it be possible to have multiple concurrent Drive instances to different Sheets. Currently I can't see how to have simultaneous response handlers for different queries, i.e. a background 'analytics' instance polling at regular intervals alongside 'player data' transactions, 'localisation' changes etc.
     
  35. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @SydInt! Sorry man I dont understand the question :S

    I think you want to have different webservice deployments to handle different cases. Thats totally good and feasible.
    But then I lost you after that. Can you elaborate?
     
  36. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Hi @Novack,

    Sorry for the unclear wording. I'm trying to simultaneously communicate from different classes for different purposes which would look something like:

    MyAnalyticsClass -> DriveResponseHandler() //Sync player stats
    MyPlayerSaveDataClass ->DriveResponseHandler() //Save data synced in cloud
    MyLocalisationDataClass ->DriveResponseHandler() //Stores localisation data

    Currently Drive's response handler action will send any responses to all three classes, i.e. a GetObject response will be received by all. Is there a way to send only Analytics responses to MyAnalyticsClass, Localisation responses to MyLocalisationDataClass and so on? At the moment Drive response doesn't indicate which class initiated the request so I have to manually disable response handlers for one class before using Drive for the next class.

    Hope this makes sense!
     
  37. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @SydInt thanks for the clarification! Makes sense now.

    From that point, I would say you're free to implement any desired approach. The approach Im using for the new asset currently under development, is to have different UnityAction events, that you can invoke (and suscribe to) depending on the response type. Each class would suscribe to the event of its interest.

    So when you initiate the request, you add an action type, and the reponse will also have it back. You can use that to filter responses and send the data to the right context through invoking the corresponding UnityAction.

    Hope that makes sense :)
     
  38. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Thanks @Novack!

    So with that approach, would two different classes receive the same response if they both were subscribed to 'getObject'? Or would they only receive the response corresponding to their request? So that Class1 doesn't get 'getObject' responses intended for Class2 and vice versa.
     
  39. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Heya @SydInt

    Any class subscribed to a type will receive the same response. The type defined in the request on the "action" key, is the one used for filtering the response as well.

    If you find yourself needing more granularity on the filtering, you can easily add a new key to the requests with a value of your convenience. Then, in the webapp code go through the return types and add the new field to the result object (see the result.query as example of how to do it).
     
  40. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Thanks @Novack - that's exactly what I needed. So to create custom requests:

    Drive.cs:
    -Add a new enum field to QueryType, i.e. 'createCustomObject1', 'createCustomObject2'
    -Create the custom request method i.e. CreateCustomObject1(string jsonObject...

    WebApp Code.gs:
    -Add case for the field in ParseFlow(e), i.e. case "createCustomObject1":
    -Create parse methods i.e. ParseCreateCustomObject1

    That sound right???
     
    Novack likes this.
  41. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
  42. SydInt

    SydInt

    Joined:
    Jan 23, 2015
    Posts:
    9
    Hi @Novack,
    Suggesting a simpler alternative:
    So we can manage all requests in a single response handler, could we pass a 'transactionID' argument to your Drive methods, i.e. form.Add("transactionID", transactionIdName).
    And have this sent back to us in HandleDriveResponse as dataContainer.transactionID. Then any class will know if the response is for them or not.
    Thoughts?
     
  43. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @SydInt yes exactly, that was what I was trying to explain originally, but I wasnt expressing myself right:

    When you do the requests on the Unity code, you can add a new key-value pair to the dictionary form, and then return that on the response (tweaking the server code), same way as result.query is being returned. Not a new query type, just a new field on the data to be sent.

    As a matter of fact, in future updates Im considering returning all the request data on the response, so this type of changes dont require server changes. Maybe with a conditional flag.

    PS: Remember to re-deploy the server webapp when you change the code.
     
  44. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Check this out:


    Isn't that cool? :)

    I have been wanting to share this Google Spreadsheets not-so-well-known feature, for its potential good use with Google Sheets For Unity: there is a simple localization example included with the asset, that could adapt this in no time!

    Some guys on Twitter, beat me to document it, so Im just sharing the video obtained from this thread:
    https://twitter.com/papa_fire/status/1111994073609633792
     
    Last edited: Apr 1, 2019
  45. VRARworld

    VRARworld

    Joined:
    Oct 28, 2018
    Posts:
    1
  46. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hello @VRARworld, sorry, I dont understand the question, nor the image. Can you elaborate?
     
  47. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    With the localisation example, I get a null reference exception when I try to run the download localisation, create localization and upload localisation functions accessed through the scriptable object's inspector gear icon. I assumed it required the drive connection prefab to be in the scene, but even when included and set up with the appropriate content, the null reference exception, (with reference to 'connectionData.webServiceUrl == "") persists. It does appear the example is only loading the data from the scriptable object, with no connection to a spreadsheet. I have not been able to figure out any other way of connecting the scriptable object to a spreadsheet. It's a bit confusing.
     
  48. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    Ooookay. Wierdly enough, I waited to be sure I could not fix this myself before posting that, but a moment after, I opened a different example scene using the same drive connection settings and ran that successfully, and then returned to the Location example, now the functions work, the null exception error still appears, but the functions work all the same. Weird.
     
    Novack likes this.
  49. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    844
    Hey @DADA_universe, thanks for reporting!

    Can you show me the NRE callstack as it shows on the console?
     
  50. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    38
    What I've noticed is that if I reload the project, the commands would fail to work again, I would have to go run one of the example projects like I described to get the web connection working again. Here's what gets logged to the console:

    NullReferenceException: Object reference not set to an instance of an object
    GoogleSheetsForUnity.Drive.SetConnectionData (Boolean runtime) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:613)
    GoogleSheetsForUnity.Drive.CreateRequest (System.Collections.Generic.Dictionary`2 dataForm, Boolean runtime) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:657)
    GoogleSheetsForUnity.Drive.CreateObjects (System.String jsonObjects, System.String objTypeName, Boolean runtime) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:168)
    GoogleSheetsForUnity.MagazineDataSO.AddAllKeysToTable () (at Assets/U-Ironies_SceneAssets/SceneScripts/GameScripts/MagazineDataSO.cs:85)