Search Unity

  1. Unity 2019.1 is now released.
    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:
    615
    Hey @DADA_universe thanks!
    That seems to indicate you have no connection data asset on the DriveConnectionEditor.cs script.

    You need to add one here:
    upload_2019-4-8_10-15-44.png

    This is slightly more detailed on the manual.

    Hope it helps!
     
  2. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    Thanks @Novack. That fixed it. I never would have guessed!
     
    Novack likes this.
  3. Wikzo-DK

    Wikzo-DK

    Joined:
    Sep 6, 2012
    Posts:
    63
    Do you recommend using this for authentication purposes? E.g., having a login screen where people type in an activation key that we have provided them and then check if it exists in our Google Sheets?
     
  4. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    615
    Hello @Wikzo-DK,

    The use case your describe is pretty simple and should work perfectly. However in more strict authentication terms, there is more I would need to understand in order to give you a more precise answer.

    If the idea is comparing a set of values with those in a spreadsheet, then without a doubt it will do. What happens next, and if the user needs some sort of stateful connection that says if the user is online, or needs to use a session token, then I need more details in order to tell you, but complexity certainly goes exponential from there.

    Hope it helps!
     
  5. Kiupe

    Kiupe

    Joined:
    Feb 1, 2013
    Posts:
    516
    Hello,

    I just took a look at the documentation and I was wondering if you plan to add methods to query rows with conditions ? I mean, be able like in traditional database to select rows where a column has a specific value. For exemple retrieving all rows where the column "id" equal "1". Does that make sense for you ?

    Extra question: would it be possible to download spreadsheet as csv (for exemple) and having the plugin use them as source instead of the online spreadsheets ?

    Thanks
     
  6. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    615
    Hello @Kiupe thanks for contacting,

    GetObjectsByField method in the API does what you ask for :)
    Is a bit rudimmentary compared with a real DB query, but works just fine for the purpose.

    Regarding other data sources like csv, nope, the plugin is done to specifically interact with the online spreadsheet, and create instances of objects on data retrieval. If you want to download it all, you can with GetAllTables(), but how you keep it in memory, storage and persistence is up to you, and the plugin wont be in the middle of it.
     
  7. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    This is not strictly a GSFU issue, but since whatever I do will have to be compatible with GSFU anyway, I think it's best to ask here.

    I'm trying to load some images from a google sheet into my scene in such a way that I can cycle through a list of images as textures, loading them as sprite into the UI canvas sequentially per button press. I've got all that set up and I can load an image as sprite using the GSFU api, but trying to populate a list of sprites and then get them to show in the right sequence with text also retrieved from the same row on the sheet, per button press is leaving me severely cross eyed. I'm using a scriptable object to store the output from the sheet locally, which works well for text because the download happens within the same time frame, but the images take longer and that throws things off kilter. How do I get the Drive.GetImageFile(); command to pull all the images from each row on the spreadsheet into the right slot in the scriptable object? I don't mind having this happen in the start function and displaying a loading screen till its done, as long as my images can then appear at the right place at the right time once everything is fully loaded.

    I'm also going to have to work with video, audio, and pdfs and I assume the same problem will apply. I'll appreciate any tips with these too. How do I download and handle these different formats without losing the reference to the appropriate text also being downloaded from the sheet?
     
    Novack likes this.
  8. Novack

    Novack

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

    How are you downloading the images from the table, do you have a link or address to them, that you later use with GSFU?
     
  9. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    I'm relying on the Drive.GetImageFile(); function and feeding into it the object ID (of the image in Google Drive) as parameter. I would have liked to use the object's url directly, because they're easier to grab and load into the sheets but I've only seen the Drive.GetImageFile() option in the GSFU api, and that requires the object ID to work with. I'm thinking I might have to use regular expressions to fish out the object ids from the urls, as messy as that may be.

    I've uploaded a screen grab of the canvas / screen I'm using to display the information from the sheets, a screen grab of a sample sheet and of the scriptable object I'm using to hold the data before loading it to the screen. The column 'medium_link' is the one that holds the object ids, anything else such as the slots that hold urls or empty cells return errors as per the code and I'm able to handle those errors by displaying a no image found texture or something like that for rows where that happens. Strings work pretty fine, but it's downloading the images and loading to screen in the right sequence that is the problem. The protracted download time means if I press the prev / next arrows before the download is done, the image will appear with the wrong data when it finally loads. What I've been trying to do is to have a 'loading' image displayed for each row that is still loading and have this replaced with the actual image when downloading is complete. Ditto having a separate image for when there is an error or no image to download. I thought having a slot on the scriptable object for the sprite to be loaded would work but I keep getting type mismatch when I try loading the sprite I create using the _text2d from Drive.GetImageFile():

    renderSprites = Sprite.Create(_text2d, new Rect(0.0f, 0.0f, _text2d.width, _text2d.height), new Vector2(0.5f, 0.5f), 100.0f);

    into the medium_Image slot for sprites in the scriptable object, which shouldn't be since renderSprites and medium_Image are both defined as sprites.

    I hope I've not just managed to make this a lot more confusing!


    screengrab.PNG
    screengrab2.PNG
    screengrab3.PNG
     
    Last edited: Jun 11, 2019
  10. indieDoroid

    indieDoroid

    Joined:
    Jan 25, 2016
    Posts:
    146
    Hi @Novack

    I'm a solo dev and a novice programmer working on a PC\Console game.

    I'm looking for a localization solution and your tool looks promising.

    Would you consider making some video tutorials to help newer devs get started with your tool?

    I managed to get your webGL version working with a public google sheet. But I'm unclear on:

    > Is "Google Sheets For Unity" accessing the google sheet at Runtime? If not, do I need to export manually a .csv or .txt file?

    > and what would the workflow be like, if I worked with a freelancer to translate into other languages. (They'll be updating the google sheet)

    Currently I wrote a simple script that reads a .txt file and asks Unity to check for line breaks, upon which the text will be updated.

    I'm not sure if I'll be able to use your tool as it's intended. (Cuz as mentioned ... a newb programmer)

    If making "how to videos" isn't feasible, would you consider releasing a free demo version that has only the base features. So that we could try out before deciding to buy "Google Sheets For Unity"?

    Unity's refund system isn't very good unfortunately.
    Thanks for your time
     
  11. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    GSFU accesses the Google sheet at runtime, so no need to export any file. The asset has a demo scene that demonstrates a use case for localisation and it's quite straight forward.
     
  12. indieDoroid

    indieDoroid

    Joined:
    Jan 25, 2016
    Posts:
    146
    @DADA_universe thanks for the response.

    Sorry newb question, I'm trying to still wrap my head around how this works.

    How would games that are on the Switch for example, get access to the dialogue? Do players need to be constantly connected to the net?

    I can understand for mobile games this is fine, since most of them require a net connection anyways.

    Thanks for the help
     
  13. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    Yes you would need connection to the internet, at least for each time you make a call to retrieve or add data to the spreadsheet. If you only needed to do that periodically though, then you would need the internet connection only for those times and not always.
     
  14. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    615
    Hey guy sorry for the late replies, time has been scarce around here.

    @DADA_universe I think I understand your problem, when you do the GetImageFile(), you dont know to what query a given returned image belongs to, so you cant be sure what to show at any given time. This looks like a GSFU design issue indeed. I didnt thought of that use case.

    However, I think we can overcome easily by adding a line to the webapp script. If you go to the Drive web service script, you'll find a function called ParseGetFile(...); just above the return line, near the end where it builds the result object, please add this line:
    Code (JavaScript):
    1. result.fileId = e.parameters.fileId.toString();
    Then save and redeploy the webapp.

    This will enhance the query response, which will include the reference to what image id the response belongs to. So in Unity when the response arrives, you can compare the Drive.DataContainer.fileId with the id of the image currently supossed to be displaying, and if its the correct one, you show it.

    In the same vein, you can make use of this to load the texture into the ScriptableObject before hand, while the game is loading (or at least a number of them, lets say the first 5, or 10).

    Hope it helps!

    I will make this change an official change to the next patch (whenever it may be).
     
  15. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    615
    Hey @indieDoroid, thank you for contacting.

    Im afraid time constraints wont allow me to produce more documentation material at the moment (such as the videos you propose) and the same apply for a demo.

    But the more fundamental limitation I think would be that the asset is not really for novice programmers. It asumes the person using it has a clear understanding of a series of concepts, so essentially the person using the asset must be a fluent coder.

    Hope it makes sense!
     
    indieDoroid likes this.
  16. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    Thanks Novack, I'm following your suggestion here. Debug.Log("this is the fileid: " + dataContainer.fileId); however only prints out "this is the fileid: " . Meaning dataContainer.fileId is not being parsed at all or is not being parsed as string. I suppose I'm meant to use this in HandleDriveResponse(); since that is where I have the reference to it? What you've described here makes sense to me, but I've not been able to make it work.

    One other thing I've since realised is that since Scriptable Objects would only work with assets already saved as an asset rather than in scene, hence the type mismatch shown when I tried to load the _text2D directly as sprite, I would have to first download / save the media as assets and then load them from a folder in the asset folder into the scriptable object. I'm handling the download part already and if I can get Drive.DataContainer.fileId to work, then I can save each image with it's fileID as it's name like this:

    Code (CSharp):
    1. byte[] bytes = tex.EncodeToPNG();
    2. File.WriteAllBytes(Application.dataPath + "/TexturesBank/" + dataContainer.fileId + ".png", bytes);
    This would enable me load all images in the folder into a list and cycle through their names to match against any file ID I'm querying to know which one to load. Hopefully, this should also work for PDFs and audio files. But first to get dataContainer.fileId to work....
     
  17. Novack

    Novack

    Joined:
    Oct 28, 2009
    Posts:
    615
    If the fileId is not being sent by the server, the server side changes took no effect, and you need to review the deployment. You should be able to debug the client to check the server result and see what is coming on the json response.

    No need to save the images btw, you can just create a struct containing the id and the texture, and loop over those.
     
    Last edited: Jun 14, 2019
  18. DADA_universe

    DADA_universe

    Joined:
    Mar 14, 2015
    Posts:
    26
    Thanks Novack, your tips helped. I figured out the change to the webapp script was not registering unless I bumped up the version number while redeploying. Also, the 'type mismatch' error that comes up in the scriptable object only affects serialisation (seeing the element in the SO slot in the inspector), the entity is actually there and can be handled by code. So I created a struct as you suggested, within the class I'm using in the scriptable object and I've achieved what I intended that way. Many thanks.
     
    Novack likes this.