[RELEASED] Google Sheets For Unity

Short newsflash:

This year Im one of the sponsors of the yearly Christmas giveaway organized by Devdog: register to win awesome prizes and share to unlock new tiers! My assets will raffle on december 20th, but if you register now you are eligible for winning prizes every day.

Follow me on Twitter for news on this and more!

 

Google Sheets for Unity is awesome. Unfortunately, I have recently encountered a problem with "Concurrency" that I am not able to understand. It looks like after just one write to my Google Sheet, any subsequent writes result in the error below. Is it possible I have to somehow "close" a connection before making another request? My spreadsheet is rather large (4000 rows), so maybe the update takes so long that it's left hanging?

The error occurs after the 2nd attempt to write to the same sheet with this call to Drive:
Drive.UpdateObjects(AllPhotosSheet, columnToMatchUID, rowUID, jsonPhotoInfo, false, true);

Is it perhaps a mistake to set the "runtime" bool to "true"?

Error stack:
in UpdateStatus(): Concurrency issues trying to process the request.
UnityEngine.Debug:Log(Object)
GoogleSheetsForUnity.Drive:UpdateStatus(String[]) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:433)
GoogleSheetsForUnity.Drive:HandleError(String, Single) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:420)
GoogleSheetsForUnity.DriverocessResponse(String, Single) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:404)
GoogleSheetsForUnity.<CoExecuteRequest>d__2:MoveNext() (at Assets/Google Sheets For Unity/Scripts/DriveConnection.cs:42)
UnityEngine.SetupCoroutine:InvokeMoveNext(IEnumerator, IntPtr) (at /Users/builduser/buildslave/unity/build/Runtime/Export/Scripting/Coroutines.cs:17)

 

Further investigation reveals that the large sheet size is the problem. When I use a smaller sheet (200 rows), the problem does not occur. What happens is that the first request times out, and then subsequent requests to write to the sheet generate the "Concurrency" error (since maybe it's still left hanging?)

I guess I'll just have to shrink my spreadsheet or start using FireBase!

The timeout failure (against the 4000 row sheet) looks like this, fyi.

in UpdateStatus(): Operation timed out, connection aborted. Check your internet connection and try again.
UnityEngine.Debug:Log(Object)
GoogleSheetsForUnity.Drive:UpdateStatus(String[]) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:435)
GoogleSheetsForUnity.Drive:HandleError(String, Single) (at Assets/Google Sheets For Unity/Scripts/Drive.cs:422)
GoogleSheetsForUnity.<CoExecuteRequest>d__2:MoveNext() (at Assets/Google Sheets For Unity/Scripts/DriveConnection.cs:29)
UnityEngine.SetupCoroutine:InvokeMoveNext(IEnumerator, IntPtr) (at /Users/builduser/buildslave/unity/build/Runtime/Export/Scripting/Coroutines.cs:17)

 

Hello there @TimeWalk-org thanks for contacting.

It is indeed a large sheet, and your idea of spliting your data among different sheets would be the way to go. Also you can raise the time out time setting on the ConnectionData asset.

In any case, if you dont seem to find a way to split your data in different tables, your're right about considering more conventional database solutions!

Hope it helps, have a Merry Christmas!

 

I currently have some questions to purchase this product ("https://novack.itch.io/google-sheets-for-unity").
1. Do you support Android, iOS, Windows, and Nintendo Switch platforms? (Any Paltforms)
2. Can I parse with Json?
3. Do you officially support Unity 2019.1.14f1?
4. Is it possible to format SpreadSheet into a specific shell?(font, size, shellsize, color, style, etc.)
5. Can you tell me the size of the current sheet?
I have a reason to split the sheet in a question from @ TimeWalk-org.
6. If I split a sheet, is it possible to search through all the sheets?
7. Is it possible to find a row containing the information I want to find by searching for a specific key value in a given column?
Example: I want to find a row with the key "Adam" in column A.
Or the value of the specified data held by "Adam" of unknown row
Wait for your reply.

 

Hey @YellowCode, it seems that I somehow missed your post. Sorry about the delay.

Regarding your questions:

1. Yes, it supports all plataforms allowed by UnityWebRequest which include all of your listed: Android, iOS, Windows, and Nintendo Switch.
2. Google Sheets For Unity returns json, yes.
3. Yes I support Unity from 2018 up the the latest stable release.
4. You can format the spreadsheet text, and it wont interefere with the data (manually formatted, not from the asset).
5. There are no predefined limits, its found while working, as it highly depends on your data structure and the response times.
6. Not by default, but you could tweak the server side googlescript webapp to do that. By default each worksheet will be considered a different table/object type.
7. Yes. In the search you specify column and value to look for.

Hope it helps!

 

Your service is perfect.
Buy immediately!

 

Glad it result useful! Remember you can check the API reference: https://novack.github.io/GSFU-API-Reference/

 

Great work.

Worked well for me for a few weeks, but then today I'm getting "Undefined server response:" with no other information. I don't believe I've changed anything..

 

Maybe it's related to the google V8 javascript thing? Note the "updated by" field on the scriptlet app - "" - and the alert message..

 

Yup - sorry for the rapid fire responses. The solution was to disable V8:

 

Hello @KaiMiraGames thanks for posting.

Woot that was fast . Faster than me at least Thanks for sharing the problem and the solution btw!

Did they enabled the new javascript runtime for you, or was an option offered? Google Apps Script can be a volatile environment, I dont recommend using brand new features right into production: in my experience that resulted in trouble, every time.

 

Heads up everyone!

Its being reported that some Firefox Nightly builds are rejecting Google Sheets For Unity requests on Unity WebGL builds, due to CORS incorrectly filtering Simple Requests. Seems like a browser bug, I will be following, but for the time being please avoid Firefox Nighlty.

 

Hallo, is it possible to have more than one Connection Data at the same time in a scene? I have two Google Sheets I want to open and read separately.

 

Hello @ratking, sorry for the late reply.

Yes, you can do that. You need to tweak the Requests related code.

First you need to create as many connection data assets as you need, then have a collection of references to them somewhere (ie: in DriveConnection class, as the original reference is stored, may be a good place).

Then you need to mod the related code, to switch the connection data dinamically, before using it. The right place to make this will probably be Drive.SetConnectionData() which does precisely what its name says, you would just need to select the desired connection data reference

Hope it helps!

 

Hi Novack, thanks for the wonderful tool. Before buying it, I have some questions hope you can help to answer.
1. Does it support Chinese characters? I tested with the web demo and the characters are displayed blank.
2. Does it support cross-platform(Android/IOS) and multiuser updating the same sheet? (I think yes, But I want to double-check)
3. This question is not directly related to your work, but I would like to know - is it possible to achieve the same function with Microsoft Excel online?

Thanks!

 

Hi @Eric0x, thanks for the kind words!

1. I have not tested it, the blank characters are most likely related to the presentation (UI) rather than the actual data in the transmited json and/or the spreadsheets. Im afraid I cant say for sure, have that concrete testing pending.

2. Indeed, it support cross plataform use on every plataform supported by UnityWebRequests, which include both of your mentioned, and more. Also, yes, the service support concurrent access, up to an extent, it all depends on the scope of your needs: if your app will have high demand, you'll be better covered by a conventional database approach.

3. No, not yet. There is some work done on their new online API, but part of the magic of Google Sheets For Unity is that you dont need to handle authentication to Google, nor include SDKs. Also, there is a matter of app and API maturity, versatility and so on. I think we will need some time before being able to consider it and equivalent option.

Cheers!

 

Whatever happened to future updates? I recently bought this due to this specific post. I'm pretty much 99% done with my implementation, but I guess I'll have to figure this one out on my own?

 

Hello @Trouler, thanks for contacting.

Not sure what you mean when you say that you purchased the asset due to that specific post. But regarding the concept described on that post, it has been introduced already, the request type is already returned on the server response, along with other context sensitive data (that varies depending the request type).

So it all comes to your specific needs. If you need a transaction id as talked on that post, that specifically has not been added, but you can introduce that, shouldnt be hard at all.

Let me know if that answers your question.

 

Hi again. Sorry, I sounded a bit grumpy in my initial post. Was commuting to work as well, so that didn't make it much better

I quoted you on the wrong post. I was also looking to get some kind of return value for images I downloaded, as I make the request from different objects, and they were taking any image they could find in the responses being sent back.

Your answer to whoever it was with returning the fileId through the Drive Script worked just fine, so thanks for that <3

 

Thanks for your message @Trouler and very glad it helped

 

Hi, @Novack I bought the tool and it works well.
But I have 2 questions.

1.
Can I send multiple items to the spreadsheet to update multiple items in one request? instead of using for loop and send then one by one?
for example, I did it as follows

Code (CSharp):

1.             for (int i = 0; i < objList.Count(); i++)
2.             {
3.                 string jsonObj = JsonUtility.ToJson(objList[i]);          
4.                 Drive.UpdateObjects(_tableName, "X", objList[i].X, jsonObj, true, true);              
5.             }

The problem with the code is, they are being sent one by one. If I have a lot of items. It will send a lot of post requests, sounds like a problem to me. Can I send it in one request?

2.
I found that if I send "create item" request and immediately send "get table" request. The "get table" request will return empty and create a bug. I think it's because the table needs some time for writing before it can be read? How can I prevent it?


Thanks!

 

Hey @Eric0x, thank you for contacting.

Yes you can update multiple objects in one call if the value to be updated is similar in the batch. In your example, you dont need the for loop, just send the call once and you'll obtain the same results!

Regarding your second issue, yes, that may be the case sometimes, the safest and recommended way is to wait for the CreateTable response, then send the CreateObject request. You do that easily by catching the "createTable" query type on the response handler, and only then sending the objects to be added.

Hope it helps!

 

Hello!
I am using this product very well!
I still have some questions.
Can you help me with this problem?

1. User is possible.

2. Can I add new sheets from the current spread with Code?

3. And it is very difficult to connect spreadsheets.
(Not well connected)
Do you plan to make a video lecture using Youtube?

I will wait for your answer

 

Hey @YellowCode, thanks for contacting, though Im not sure I understand your questions!

Sure thing, you can add new worksheets on the spreadsheet by code. Currently, I dont have plans to produce audiovisual documentation material, sorry

That being said, the multiple included examples, the online demo, the included Developer Guide, and the online API docs should help great deal understanding the asset.

Hope it helps!

 

Hey, I really like your Plugin so far, but I have some questions.

1) How can I change my spreadsheet connection at runtime? I tried several things, like changing the assigned ConnectionData, using the login function, etc, but my Drive object keeps accessing the same spreadsheet.
I need to insert the same row in several spreadsheets.

2) I found a post in this thread that said that the plugin can't handle large Google Sheets documents. So I tested it and I'm really disappointed with the fact, that just 250 lines break the usability completely. I wouldn't call that a big sheets document at all. So if I can't fix this somehow, the Plugin is sadly completely unusable for any of my planned purposes. What is the problem there? I'm really just trying to update a text in my localization document from the script and get the "Concurrency issues trying to process the request." error on a simple Drive.UpdateObjects command. I understand that using smaller sheets will fix this, but I can't divide everything into 200 line chunks.

 

Hello @retraffic, thanks for contacting.

I think your issues are being related to some sort of misconceptions or errors, because the stuff you are finding issues with, are perfectly feasible.

In the first case, you should be able to change your spreadsheet freely (many devs already work like that). To do that you can do as I described on a previous post on this topic:

Or also, you can simply change the spreadsheet id at the connnectionData asset before creating the request. This has not much complexity in an on itself.


On the second account, I have tried spreadsheets with thousands of rows and multiple sheets without problem. When you say "250 lines break the usability completely", can you elaborate more on what actually breaks for you? Because that doesnt make sense.

Common sense would indicate its not the amount of rows but the amount of data that will come to show the limits, so just in case let me ask: whats the size of your row?

I think I need more elaboration on your part regarding the concrete problems you experience. You can contact me by email as well, address is on the dev guide.

 

1) Setting the id in code seems to work (but just changing the connectiondata in editor doesn't):

Code (CSharp):

1.  if (Drive.driveConnectorRuntime != null)
2.         {
3.             Drive.driveConnectorRuntime.connectionData.spreadsheetId = spreadsheet;
4.         } else
5.         {
6.             GetComponent<DriveConnection>().connectionData.spreadsheetId = spreadsheet;
7.  
8.         }
9.  

One thing fixed.


2) I can't really believe your "thousands of rows" comment. My rows really just consist of five columns with less than 10 characters in their content. After adding just a few rows, the for-loop that searches for the identifier just becomes super slow. This one:

  for (var i = startRow; i < dataLength + 1; i++)

  {

    if (dataRange.getCell(i, searchFieldIdx + 1).getValue() == searchValue)

    {

[...]

    }

 }

so I've changed the google script to return the row number when creating and updating rows and added a row number parameter to the update function to quickly edit the specified row without having to search every line. Of course, this produces problems when removing lines or resorting the spreadsheet, but this way there shouldn't really be a limit to how large the sheet can be.

 

In editor? Not sure what you trying to do here.
As said, you need to find the right point where to do it, right before the connection data is loaded to create the request. Very basic logic flow analisys.

As for the rest, you dont elaborate in what is it thats failing for you. You describe the issue as "super slow", which doesnt really describe the situation. Please elaborate on what you consider slow, what operation is behaving like that, and if that is happening on the stock asset, or in your particular project implementation.

I just took the stock asset, run the included "Spreadheet Example" scene with a sheet previously loaded with 5000 rows, and it worked wihtout issues.

 

Well if you don't want to understand that what you call "Very basic logic flow analysis" isn't working, then I can't help you. I told you that what you described is what I was doing. It's really not all that complicated, but it just doesn't work how you describe it. My code above will work for anybody wondering.

I feel like I was very specific in my descriptions of what was slow. I pointed you to the function in the google script, told you the exact amount of rows in my sheet, how many characters are in each cell and that after using the Drive.UpdateObjects command without changing the google script code, I only got an error stating "Concurrency issues trying to process the request.", which I found out is only there because you hardcoded a timeout of 10 seconds into the google script. After taking out that timeout, I tested that the script running time is more like 30 seconds on a sheet with 5000 rows. I added a "break;" into the above mentioned for loop because I didn't really need to edit more lines at once and there was no "UpdateObject" command. This improved the issue for rows located at the top of the document, but for rows, at the end of the document, it was still going to take a very long time.

Anyways. I got it figured out by myself, but I really don't appreciate you acting like this is all just my imagination. I really want to point out that I didn't have this problem at all because my sheets aren't that big yet but they will grow over the course of development and I read in this forum thread, that somebody else had an issue with bigger sheets files.

So I got to the problem recreated with really the simplest of all scripts, just one line copied from your example script and a simple five column sheet with 250 rows.

Code (CSharp):

1.                   Drive.UpdateObjects("Passages","uniqueIdentifier",person.id, jsonPassage, true, true);

(This is not my actual code, because I changed your UpdateObjects function on my end since, to allow for the passing of the exact row number)

So just not acknowledging that there is an issue with that won't really help you in the long run. I really don't understand why you are so on the fence regarding the issue. I gave you a solution after all.

 

Glad you solved it, good luck mate!

 

Wow.

 

Hi. I am building Recovery Treatments for Human Health problems by VR games and my main goal is that the doctor has access to a Google Spreadsheet and that this one read that file and update its parameters if it was changed according to the game and the doctors also can see the patient evolution in this Google document .I am thinking in building it with your plugin. Do you think if it would be possible? Thanks for your time. Alejandro

 

Hello Alejandro @castana1962, thanks for contacting!

I think it should be perfectly feasible, though Im dont know the details on the patients evolution, there would be a worksheet table with each row being the data for a different patient, I presume?

The use case seems a green light!
Hope it helps

 

Hi Novack
First, thanks for your fast answer...
Yes, You are presuming right....
On the other hand, I am very excited to work ( or try if it is posible) with your plugin.
I am building Recovery Treatments for Motor and Cognitive problems by VR Games and it is very important for me, give to the doctors some tool for that these ones can see the patient evolution through similar table in gif file.
Since I am new in the Google Sheets topic, if you could help me to make it, It would be great !!
Thank you.
Alejandro
Ps. Sorry for my little english

 

Alejandro @castana1962, podemos hablar en español, contactame por mail

Also, you can try the online demo at the asset page, and check the docs at https://novack.github.io/GSFU-API-Reference/

 

Hola Novack
Que bueno !!!
Mi ingles no es demasiado bueno
Te estoy enviando un email a info@sidgin.com
Estamos en contacto
Muchas gracias
Alejandro

 

No formo parte de esa empresa, es otro plugin. Este es el mío: info@cyberiansoftware.com.ar

 

Ahh. Ok. no sabia eso. Estoy enviando a ese email. Estamos en contacto. Gracias. Saludos

 

Hi @Novack,
I'm using "Lite" and it's very convenient and working fine.
However, due to the change in my team rules, the use of "Any one, even anonymous" as the web application publishing setting has been prohibited.
Is there any way to connect other than "Anyone, even anonymous"?
(If it is already possible with the "original version", we will purchase it and consider replacing it.)

What I tried
---
Get OAuthToken in GAS.
function GetOAuthToken() {
Logger.log(ScriptApp.getOAuthToken());
}
---
"Drive.cs" Add OAuthToken obtained by GAS.
unityWebRequest = UnityWebRequest.Post(_connectionData.webServiceUrl, form);
unityWebRequest.SetRequestHeader("Authorization", "Bearer "+ OAuthToken obtained with GAS);
---
You can connect, but only until the OAuthToken expires.

 

Hello @t_fujioka thank you for contacting.

I have not dived into alternative authorization options to access the endpoint, because one of the primary ideas behind GSFU is precisely to remove the authentication procedures and related delays from the equation.

I have only read superficially about what you already have tried, and Im not sure about extra alternative options.

That being said, you could add parameters to the endpoint as rudimentary origin checks. This is already done as example in the "passcode" parameter. This way you could keep the annon access, but entirely depends on your scenario, and if the url is going to be public.

 

Hi @Novack;
I'm using your plugin and works very nice.
But, yesterday I was thinking in how many requests I can do to Google.
Do you know how many requests I can do to my spreadsheet?

I would like to know your answer because I don't know if you Google App Script supports more requests or this thing is about Google.

Thank you!!

 

Hey @SrTartarus21, thanks for contacting

Here is the source info regarding Google script quotas and limitations.
Mostly, the issue you may find is related to simultaneous calls/executions.

Hope it helps!

 

Hi @Novack ,
I bought your GSFU asset and it's super useful. But I have a question. I try to reuse your example code to inquiry a row data which has specific string mentioned in a GameObject. This is the first script, reusing lines from SpreadsheetExample.cs:

Code (CSharp):

1. public struct BimObjectInfo
2.     {
3.         public string instid;
4.         public string category;
5.         public string typename;
6.         public string level;
7.         public float volume;
8.         public float area;
9.     }
10. [HideInInspector] public BimObjectInfo _bimObjectInfo = new BimObjectInfo { instid = "0", category = "Sample", typename = "sample", level = "Level 0", area = 1.1f, volume = 2.2f};
11. ...
12. public void RetrieveDataArea(string instanceid)
13.     {
14.      Drive.GetObjectsByField(_tableName, "instid", instanceid, true);
15.     }

Then, I put another script in the GameObject to call the data:

Code (CSharp):

1. public SpreadsheetLink sLink;
2. public string thisid;
3. public void Start()
4.     {
5.         sLink.RetrieveDataArea(thisid);
6.         Debug.Log("<color=yellow>Object retrieved from the cloud and parsed: \n</color>" +
7.                   "ID: " + sLink._bimObjectInfo.instid + "\n" +
8.                   "Category: " + sLink._bimObjectInfo.category + "\n" +
9.                   "Name: " + sLink._bimObjectInfo.typename + "\n" +
10.                   "Level: " + sLink._bimObjectInfo.level + "\n" +
11.                   "Area: " + sLink._bimObjectInfo.area + "\n" +
12.                   "Volume: " + sLink._bimObjectInfo.volume + "\n");
13.     }

But, the Console shows the sample data from the first script, not the row data that I want which is the one which has

thisid

that associates with

instanceid

.

Object retrieved from the cloud and parsed:
ID:
Category: Sample
Name:
Level: Level 0
Area: 0
Volume: 2.2

Can you help to identify what's wrong with my code?
Thank you very much!

 

Hello @falfistrife, thanks for contacting and apologies for the delay coming back to you.

I answered your email. Im afraid I dont entirely follow the issue, will need some more data and more context in order to help.

 

Heads up! The old classic is on sale:
With 6+ years published and evolving, Google Sheets For Unity Lite is **50% Off** at the Unity Asset Store

The Lite edition shares the feature set of the Full version in regards to Google Spreadsheets. If you dont need to handle Google Drive files, images, or you prefer to buy assets on the Unity Asset Store, then the Lite version is your choice

Grab it on the asset page: HERE.

More info on the asset thread here.

As always you can check:
• The public API reference.
• The demo!

 

Hi @Novack can you sir please share what's the difference between Full version and Lite version? I mean can you please share list of features that Lite version lacks but full version have. I just want to know if I should buy a full or lite version based on different scenarios of my projects which aren't exactly finalized yet.

 

Hello @umair21 thanks for contacting.

The lite version is actually the original Google Sheets For Unity. Is now rebranded "Lite" edition, to denote the difference with the new main version, which extends the capabilities of GSFU. The full version includes features 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, into the handling of files and images on Google Drive.

Also, the Lite version is available on the Unity Asset Store, while the new version is available in Itch.io.

Hope it helps!

 

Thank you so much for your response. Definitely going for main version.

 

Hi mate, will this read/write back on a HTML5 build? Looking to build something that can be used on mobiles / web. Cheers.