[RELEASED] Google Sheets For Unity

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:

​

This is slightly more detailed on the manual.

Hope it helps!

 

Thanks @Novack. That fixed it. I never would have guessed!

 

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?

 

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!

 

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

 

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.

 

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?

 

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?

 

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!

 

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

 

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.

 

@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

 

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.

 

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).

 

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!

 

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....

 

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.

 

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.

 

Still on working with media, from what I can glean from the files example, it seems to be set up such that you need to have separate code for deserializing each file type (in which case you need to know before hand what file type you are expecting for each case) before you can deal with different media (.jpg, .mp3, .mp4, .pdf, etc) Given how many formats there are out there and the different issues with deserialisation, etc, and when deaing with different media, all you have to go by is the cloud file id which tells you nothing about the file type it references, I'm wondering if we could have a variable much like the fileID, which indicates what sort of media you're dealing with and then have code for deserialising which switches between media types and appropriate functions for deserialization. @Novack any chance of impementing something like this soon, or pointers on how to work around it?

 

Hey DADA_universe,

If I understood the question correctly, you can ignore it. Serialization/deserialization only takes into account one thing: text or binary format. The media type is entirely abstract to this, and you should not worry about it at all. You can simply use the file extension to denote what to do with the file once downloaded if that is what you mean.

Let me know if that makes sense.

 

Hi @Novack, love the app so far. I'm wondering how I can establish multiple connections in one scene. My game wants to pull data from multiple different Google Sheets files (entirely new spreadsheet IDs). I created multiple ConnectionData assets and entered each one's spreadsheet ID. How do I add all those connections to the scene? I tried adding multiple DriveConnection GameObjects to the scene and I tried adding multiple instances of the DriveConnection script to the DriveConnection GameObject. For example, we have a list of Shirts in one Google Sheet file and a list of Hats in a different Google Sheet file, but we need data from both in one scene. How do I accomplish this? Thank you!

EDIT: Never mind, we decided to consolidate everything into one file for optimization. Thanks!

 

Just bought the asset yesterday. I am trying to get Cell value with "GetCellValue"

Eg:
Drive.GetCellValue(_tableName, "i", "2", true);

But this is a void method, how can I store it into a String?

 

@mcteapot hey there, sorry for the late reply!

In case you need it in the future, its just a matter of assigning the required ConnectionData field to the DriveConnection script before you create a request. This can be done dinamically, you just need the references to the different connnectionData assets stored somewhere. Or you can elaborate upon that and create some mechanic that determines by itself which request should use which connectionData (look at Drive.SetConnectionData).

Hope it helps!

 

@Zaq indeed the request do not return any data, web requests are asynchronous, so it waits on the background for a response from the server, and when it arrives it will invoke a callback to which you should suscribe (Drive.responseCallback). Check the examples to see how this works, but is pretty much standard for web communication.

Hope it helps.

 

Hi,

Can this tool parse data to json file format?

 

Hello @novaVision it does, but not sure exactly what you mean. Data coming from the spreadsheet? Data going to the spreadsheet?

Google Sheets for Unity uses json for data serialization, all the comunication is done using json format, but mostly for internal purposes. What would be the use of the json for in your case?

 

In my case I just wish to grab values from specific keys (columns). I been planning to use JSON to grab these values, but may be your asset provide better solution just requesting the key value from API

 

@novaVision The asset includes a simple but relatively flexible API (you can check it here).
You basically have two options:

• Treating the sheets as databases, the asset API will consider the rows as objects, columns as fields, etc.
• Accesing the sheets directly by cell, chessboard style.

You can check the demo using a spreadsheet of your own, and check for yourself.

Please do let me know if you have further questions.

 

I have purchased the asset, but what about this step? screenshot

 

You're in the wrong tab. Its the "Script Properties", not the "User Properties".

 

Hi @Novack

I've been away from my project with GSFU for a while, trying to get back to it.
I noticed the latest update to the lite version on the asset store was as recent as August. Any intended updates for the full package currently?

Also, I've been looking at Unity's addressable asset system and wondering how GSFU might sit within that pipeline.

 

@DADA_universe hey there mate, how its going?

The recent changes shown on the asset store page are only meta data corrections (asset description), sorry about Unity Asset Store raising a false alarm.

Im afraid Im at the part of the year when I rarely get any time for GSFU development, so its safe to use the current version, nothing incoming soon!

 

Okay, thanks for clarifying. No pressure!

 

Does anyone have a solution for encrypting the passwords in the 'credentials' sheet with this? Plain text = very bad!

 

@Zapgun the questions is: why? that code is only for you, and your team. It is transmited through SSL, so it cannot be eavesdropped, or inspected. Think of it as a small payload used to validate consistency.

Regardless, if you prefer to hash it, you can do it on the client, and send the hash result, then store that on the spreadsheet for the webapp to compare. I cant see much point to it though, would be the same than any other random string.

 

Best practices? Other people's passwords should never be stored in human readable form, period. And under the GDRP, if an angry someone on my team (or a hacker) posted those plaintext passwords on the internet, I would probably be liable.

Plus, I personally don't want myself of anyone dealing with my project to view user passwords that could potentially be used elsewhere.

Regardless, the solution you suggest isn't a bad one (as long as the hash is something the client can decide on).

Cheers.

 

Just for clarification, is not "other people passwords": it is just one, universally used by the system on all requests, as an extra match check with the client.

This is something I will review on future updates, as to avoid confusion and offer more options like you mention.

Thanks!

 

Did you know...? (Tip)

You can retrieve data resulting from formulas on the spreadsheet. You can also set formulas on the spreadsheet too.

You can test this on the online public demo. First define a spreadsheet id, create a worksheet using the button on the deni UI for that purpose, and then use the "Individual Cell Operations" section to test this:

1) Define a formula


2) Retrieve the result value:


Head to the asset page, and check the WebGL demo for yourself.

 

Did you know...? (Tip)

Google Sheets for Unity will work on any UnityWebRequest supported plataform. Currently enumerated as:

• All versions of the Editor and Standalone players
• WebGL
• Mobile platforms: iOS, Android
• Universal Windows Platform
• PS4 and PSVita
• XboxOne
• Nintendo Switch

Head to the asset page, and check the WebGL demo!

 

ArgumentOutOfRangeException: Length cannot be less than zero.
Parameter name: length
System.String.Substring (System.Int32 startIndex, System.Int32 length) (at <599589bf4ce248909b8a14cbe4a2034e>:0)
GoogleSheetsToUnity.GSTU_SpreadsheetResponce.StartCell () (at Assets/Google Sheets to Unity/Scripts/v4/Spreadsheet.cs:130)
GoogleSheetsToUnity.GstuSpreadSheet..ctor (GoogleSheetsToUnity.GSTU_SpreadsheetResponce data, System.String titleColumn, System.Int32 titleRow) (at Assets/Google Sheets to Unity/Scripts/v4/Spreadsheet.cs:316)
GoogleSheetsToUnity.SpreadsheetManager+<Read>d__2.MoveNext () (at Assets/Google Sheets to Unity/Scripts/v4/Private/SpreadsheetManager.cs:121)
GoogleSheetsToUnity.ThirdPary.TaskManager+TaskState+<CallWrapper>d__17.MoveNext () (at Assets/Google Sheets to Unity/Scripts/Utils/Task.cs:156)
UnityEngine.SetupCoroutine.InvokeMoveNext (System.Collections.IEnumerator enumerator, System.IntPtr returnValueAddress) (at /Users/builduser/buildslave/unity/build/Runtime/Export/Scripting/Coroutines.cs:17)

Does this mean that the script can't access anything at that specific spreadsheet ID and sheet name?

 

Im afraid that is not my asset. Google Sheets For Unity is the original, has nearly 6 years on the Unity Asset Store, and is also on itch.io

The one you are using is a different one that was named very similarly on purpose, although it doesnt have the same feature set.

 

Hi there - is there an easy way to search a spreadsheet for a string in one column (e.g. "A") - and if found, give you the cell that it was found in? I've managed to get searching down using the GetObjectsByField command... but I can't figure out how to add my own events if the string isn't found. At the moment I'm doing a crazy work-around where it checks if the Drive._currentStatus == "Object not found." Thought there might be a better way!

I've been using the other Google Sheets Unity plugin (the free one) and it was super easy to jump into and customize - only problem is that it didn't work for mobile (well anything other than mono) so its a no-go.


If possible - it would be much easier for me to pull the entire worksheet into a public array or list and then search it from there if that is possible?

 

Hello @mitchtbh, sorry for the delay.

Regarding your first question: There is no API method for that, no. You can study the webapp and add it, the code is open and should receive well custom needs, I wrote it as clear as possible for this purpose. Remember that when you modify the google script code, you need to deploy again, a new version.

Regarding your second question, handling responses for not found objects: its processed like an error. Errors have their own callback, to which you can suscribe and receive events data. On this error callback Im indeed only sending dataContainer.msg ("Object not found."), but you can send the whole dataContainer instead, and check the query type for an improved flow. I will annotate this for the next update (no ETA available atm).

As far as your third question, there is an API method for retrieving the whole spreadsheet, GetAllTables() you can read more about it on the puclic API docs. Have in mind that for each table (worksheet) you need a corresponding class with fields matching the column headers on the worksheet, as usual.

Hope it helps!

 

Hi @Novack
I´ve been using GSFU for a while now and everything has been going on smoothly until the past 2 days. I've been getting this messages:

• "Operation timed out, connection aborted. Check your internet connection and try again."
• "Unable to complete SSL connection"

The errors appear pretty randomly. For example, out the same 3 web requests, 2 had connection errors and one was successful.

This is the scenario and what I've tried so far:

• It's just happening at my workplace but we have a pretty fast internet connection. Tried it at home and at my clients, and it works fine.
• The error occurs for builds and in editor.
• It started 2 days ago and we have not made any changes to our network or internet provider or the code.
• Was using GSFU v2.0, tried with the latest GSFU and the Sheets Example scene and the error persists.
• Increased the timeout limit to 30 secs, the error persists.
• Restarted the router... nothing.

I'm out of ideas and really have no clue how to track down this issue.

I'll appreciate any help!

 

Hello @JuanMaldonado sorry for the delay, dayjob has been pretty busy!

Im afraid its the first time Im seeing this. What version of Unity are you using? Have you recently updated Unity or any other library, asset or package on the project?

Also important: how many requests are you sending to the google services per minute and per day?

 

Random question: Any thoughts about how or where GSFU fits into Unity's Addressables pipeline? Any experiments to learn from out there in the wild?

 

Hey @DADA_universe, for my day job I have recently been checking Addressables.

It left me with an impression of not-yet-there for production, and we decided to let it mature more time before going full dive-in. I found the primary idea really great, and the overall approach to follow the premise, but the current implementation status has some issues yet. Anything beyond the basic example shows annoyances, there are quite a few settings of uncertain effects, some troubles on basic scenarios like caching issues for WebGL and TextMeshPro problems. There are some things you have to do everytime manually, which leaves you wondering why isnt done internally for you. The terminology sucks entirely, and it seems to make sense only to the fellas who were there from day one and followed the trail up to this point. So we decided to give it some more time and keep using AssetBundles for now.

Anyway, the fit of GSFU into that lies entirely on your setup. In my current day job there was already a custom setup when I arrived: a set of google spreadsheets working as DB front end, and Asset Bundles handling the content side of the data.

The thing with Addressables (and Asset Bundles) is that you still need to build and upload them if you need to update information, while the spreadsheets are dynamic by nature. So I found the Unity systems very convenient for media and unity data (images, video, audio, prefabs), and Google Sheets For Unity still covers the dynamic "pure data" side pretty well on that scheme, without the need of deployments other than updating the data itself, so is still a complementary relationship.

 

Hi @Novack
I think the issue has something to do with my ISP since it's only happening at my workplace.

Now that you metion the amount of requests per hour or per day, I'm getting concerned I might reach a quota limit. Currently we are within 500-1000 requests per day but the amount might go up as we build more projects.

Found this link for quotas: https://developers.google.com/apps-script/guides/services/quotas
I'm just dealing with simple quick requests like getting or writing row values.

1. How do I get an excepiton message in Unity if the quota is exceeded.?
2. What exceptions messages should I be excepting if quota is exceeded?
3. What type of quota limitation should I be for the lookout?

 

Thank you Novack, this helps put things in perspective.

 

Hi @JuanMaldonado I think you're safe around those numbers. I have found that those quota limits are more of a guideline, and the most relevant to us would be the one related to simultaneous executions (which seems fairly low), but I have never found to reach the limits.

There is some ambiguity and lack of concrete details for our use cases, but I know of apps with thousands of users having no issues. The fact is however, there is a point where your are better off with a more conventional approach anyway, so I think is fair to say that is unlikely you face issues with the quotas before needing a migration to some DB environment for other structural reasons.

Regarding the exception handling, at the end of the link you sent there are the messages expected from google, which GSFU will handle basically as error messages, that you will be able to inspect in any way you want. You can parse the errors response contents, looking for those messages and generate a different warning pipeline if desired.