[RELEASED] Google Sheets For Unity Lite

No problem. I needed the fix just as badly as you did, haha. Otherwise I couldn't release test builds for my current project on iOS. Hopefully the next release of Unity will take care of it properly
Thanks for the help troubleshooting!

 

Hi Novack, i just purchased this and am trying to connect using the supplied connection Example. I know im doing something stupid but i keep getting the error "Data error: could not parse retrieved data as json."
I have done the following: Downloaded the sample spreadsheet, Shared the link within google spreadsheets, (made it available to anyone) verified the passcode ( left it as it was). Edited the code in Visual studio with the correct Web Service Url , checked the spreadsheet name (renamed it GSFU)
Any help would be appreciated

 

Hello @markkivits

For what you say, you have not read the manual. The spreadsheet does not need to be shared to make GSFU work.

I would ask you to follow the manual step by step. Is short and to the point, so don't worry, there is no bibles to read
The most error prone situation is the first deployment, so is important to follow the instructions right.

If after doing an strict step by step you still have issues, please contact me by the suport email, and we can work through it.

 

Thanks Novack, will re-read and try again

 

Novack, many thanks. I realized my spreadsheet id value was incorrect and after fixing this was able to connect. This is brilliant

 

Very glad it worked

Good luck with your project!

 

@jinhwan ahn Please do not post links, code, or other any asset private information.

There is an issue with the PDF manual, when you copy the link it does not copy the url correctly.
Either you type it manually, or you contact me by email and I will point you to the correct url.

 

Hi Novack,

I sent you an email to the address on the Unity store. I suspect that I'm being an idiot, but while I can get the example classes on the web app to work with the example Unity connection script, even after I've changed some of the values, when i create my own class in the web app and redeploy, it won't work. I've copied the body of the other classes (specifically the setdata class), and it still doesn't work, and if I reference the SetData class from Unity it works. Could you point me in the right direction?

 

Just for reference, we solved the @Woyzeck1 issue through email.
Good luck!

 

Hello @Novack,
Do you have any news about the upcoming update? Has the Unity team done something about the http bug? Also, I am curious if I could use Best HTTP plugin to work with GSFU. Let me know if you have any advice on that.

 

Hello @Victor_Kallai

Regrettably, I have zero news about the bugs. The guys may be working on it, after contacting the person in charge at the forums a month ago, he mentioned he was going to work on it.

But whats worst, the window of oportunity to make this sizable refactoring has closed for me: two and a half months later my reality is different, Im about to start client work and the time for a project like this is not on the short term horizon.

I have not dismissed the update at all, but will need to re configure my next stop between works to see if the conditions to make are there.

 

Regarding Best HTTP plugin: I dont have experience with it at all, but I can't see why it would not work. You would have to refactor the connection code from the Unity side, but the web service should be able to handle incoming connections no matter the source of it.

At the current state of things though, there would be very little gains in doing so, except you have some specific needs, or want to handle all connections using the same API, in which case, again, there should be no problem.

 

@Novack thanks for the reply, I'm sorry to hear that the bug is still unresolved. Cheers!

 

I'm choosing between this and G2U - which has extensive documentation.

Having trouble finding any documentation for this plugin...

 

Hello @hawken

GSFU documentation is short and to the point, and describes the deployment steps rather than its use, because the asset objective is precise and simple. The features does not require much explanation nor extensive descriptions. So far, I have not made the docs public as I see no point on sharing the deployment specs.

G2U, is a more abarcative asset that offers options beyond a connection between Unity and Google Spreadsheets, and Shane has make a fantastic work on it.

I think the feature set of both assets overlap in a very tiny segment, and which one to take, depends entirely on your needs.

If you find the info about GSFU insufficient to make a decision, or obscure in any way, please do tell me so. Or if prefer, you can also send me a PM and we can elaborate further.

Thanks!

 

Just a heads up:
Next week I will not be around, so I will be attending any support related issues on the first week of march.

Have a great week!

 

Hello!
Working a bit with the scripts, I found that I really need the ability to grab multiple (preferably all) individual sheets from the Google Spreadsheet. I read in the docs that you can "extend the connection class" to grab multiple sheets, which I'm accomplishing currently by using a "sheet name" string array and making many WWW connections simultaneously with them, but it feels incredibly inefficient and it makes completion callbacks a little complicated to process.

The stringbuilder for the URL in the WWW request specifies an individual sheet name right off the bat, and there aren't really alternate address examples to look at, so I've been working under the assumption that specifying the sheet name in the request is necessary to retrieve the data- or that if it is possible to leave it off, it would return a slightly different type (JsonData[][] perhaps? or some other higher level container for all of the JsonData[] sheets).

If you have any tips on accomplishing this, I would very much appreciate it!

 

Hello @Lysander

As per the structure of the example, and what you need to achieve, your steps are the logic path.

Indeed, the stock webservice requires you to include a sheet name, as a flexibility option in case of wanting differrent sheets at dfferent points. However if you need all the worksheets it indeed will be ineficient.

At that point you will need to work on the web service code first. You basically need to adequate it to your needs by changing the section that looks for the data range in the specified worksheet, by something that gets all the data.

The implementation may vary depending on your needs, but you will notice in the webservice code that there are calls to a Class "Spreadsheet". This class has a getSheets() method which returns all the sheets in the spreadsheet, in an array of type Sheet[]. What to do with that data, how, and what to return depends on your needs, but looking at the stock web service and this hints should help you out to start.

 

Managed to get it working. For any others who need to do something similar, here are the steps I took.

First, a few things with the web app. Add a new check to the ParseRequest function like so:

Code (JavaScript):

1. if (e.parameters.action == "GetAllData")
2.     result = GetAllData(e);

Then add a new function like so:

Code (CSharp):

1. function GetAllData(e)
2. {
3.     var ss = SpreadsheetApp.openById(e.parameters.ssid);
4.     var sheets = ss.getSheets();
5.  
6.     var sheetData = {}
7.     for(var i = 0; i < sheets.length; i++)
8.     {
9.         if(sheets[i].getName() == "passcode")
10.             continue;
11.  
12.         var dataRange = sheets[i].getDataRange().offset(1, 0, sheets[i].getDataRange().getNumRows()-1);
13.         sheetData[sheets[i].getName()] = getRowsData(sheets[i], dataRange);
14.     }
15.  
16.     return sheetData;
17. }

This will allow us to grab all of the sheets from the Spreadsheet and shove them into a dictionary set EXCEPT the one named "passcode"- you should change this exception to fit with whatever name you use for your password sheet.

Back in the example connection script in Unity, just change the connection string to something like:

Code (CSharp):

1. string connectionString = webServiceUrl + "?ssid=" + spreadsheetId + "&pass=" + password + "&action=GetAllData";

and change the ssObjects definition to:

Code (CSharp):

1. Dictionary<string, JsonData[]> ssObjects;

and finally change the data parse with the "response" string here:

Code (CSharp):

1. ssObjects = JsonMapper.ToObject<Dictionary<string, JsonData[]>>(response);

The result works almost exactly the same way that the JsonData[] result did originally, but with all sheets, contained in a Dictionary with the string key being the name of the sheet you want to access. ^_^

 

Thanks for this. Previously I just looped the connection with different different connection strings. It took around 10 seconds for me to fetch 4 different sheets. How does this perform? Is it faster to load the whole workbook, compared to fetching it one at a time?

 

It's noticeably faster even with small data sets (or maybe especially with small data sets), but as I'm only using it during production and will be disabling it for builds, shaving off a few seconds loading is no huge deal. More important to me is how much easier it is to handle processing the data. I'm using the different sheets for different versions of the same dataset (for localization), so it makes more sense to just loop through and process all of it rather than trying to do it piecemeal.

 

Well done man!

 

Hi,
I couldn't see anywhere the relevant info.
Can you please specify the accepted data formats? I need image files to upload, is it possible?

Thanks

 

The asset functionality is described in detail. What exactly do you mean by "the relevant info"? For specific questions, like yours, this is the better space.

Uploading images to a google spreadsheet using GSFU is feasible but not so simple, and I dont include examples of such case in the asset. You would have to elaborate the webservice driver in a non-trivial way, and I only recommend that for someone with experience and confortable working with Google cloud environment.

 

To elaborate a bit:

Google spreadsheet allow images to be inserted on it, but not stored on the spreadsheet itself. That means you would need to first send/create the image in google drive, and then insert that image on the spreasheet. Not sure if this works for your purpose, but in any case, it takes a bit of work.

I have been waiting for Unity to fix some issues in the latest web request API to be able to improve some things. An example of uploading images could be interesting, but I would not use the old api, so I have no options but to wait for the new to be fixed.

 

Allright, thanks for your answer!

 

Currently using Unity 5.3.4p4 and can't retrieve data. I am using a fresh project with the ConnTest scene and provided scripts. I am able to send the ball bounce data just fine, just not able to get the ball colors. Shouldn't the retrieval of data work if the sending of data works? Let me know if there's any other info that'd be helpful.

- The Error -
Data error: could not parse retrieved data as json.
UnityEngine.Debug:LogError(Object)
<GetData>c__Iterator0:MoveNext() (at Assets/GSFU/Scripts/UnityDataConnector.cs:116)

- Connection Response -
1.351205 : <!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>Error</title><style type="text/css">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body><div><img src="//ssl.gstatic.com/docs/script/images/logo.png"></div><center>TypeError: Cannot call method &quot;getDataRange&quot; of null. (line 54, file &quot;Code&quot;, project &quot;Copy of Test GSpreadsheetConnector&quot</center></body></html>
UnityEngine.Debug:Log(Object)
<GetData>c__Iterator0:MoveNext() (at Assets/GSFU/Scripts/UnityDataConnector.cs:98)

 

@Sethers this kind of errors are always related to an incorrect setup. This one may indicate some issue on the spreadsheet, but would need a few more details, like if you changed anything on the worksheet with the example data.

In order to speak more freely about technical details, please contact me throught email, at the address detailed on the asset.

 

We figured it out gang! I was putting UnityConnectorTestData for the worksheet name, when Balls was the worksheet name. UnityConnectorTestData was the spreadsheet name. Thanks again Novak!

 

Hi.
When I tried your SendDate code in your example, it noted on a new row successfully.
But is there a way to replace data in a exist row?
In example in row 2 I have a number "1". How dow I change it to "2" , instead of note "2" to row 3?
Thanks

 

Hello @NKidd

Back in november I started designing an update for the extension that would include a complete CRUD implementation. Announced it on december, but stomped with a few bugs on the new Unity web request API that delayed the whole thing.

Updating values (besides creating and retrieving) is definitively feasible, but not included in the current example. You would have to enhance, tweak or refactor some pieces of the web service.

 

Hi @Novack
Thanks for your reply. I understand that I have to do it myself, but could you give me some tips please, as a experienced one?

I added this method to AppScript:

Code (JavaScript):

1. function UpdateACell (e) {
2.   var ss = SpreadsheetApp.openById(e.parameters.ssid);
3.   var sheet = ss.getSheetByName(e.parameters.sheet);
4.  
5.   var thePX = e.parameters.val1;
6.   var thePY = e.parameters.val2;
7.   var theNewValue = e.parameters.val3;
8.  
9.   var cell = sheet.getRange(thePX, thePY);
10.   cell.setValue(theNewValue);
11.  
12.   return "UPDATE OK";
13. }

And use this to call:

Code (CSharp):

1. string connectionString =     webServiceUrl +
2.             "?ssid=" + spreadsheetId +
3.             "&sheet=" + statisticsWorksheetName +
4.             "&pass=" + password +
5.             "&val1=" + cellx +
6.             "&val2=" + celly +
7.             "&val3=" + udValue +
8.             "&action=UpdateACell";

But it result "undefined". Could you help me please?

 

Hey! Yeah, sorry I misunderstood your question. Of course I can point you in the right direction.

The code you just pasted should work. Have in mind that for updating a certain value that way, you need to know before hand the cell coordinates on the spreadsheet, of the value you want to update. It works, but is not really a real database approach. I hope to have an improved solution for the next verion of GSFU. I need to wait for the new Unity api to consolidate though, issues keep coming up on the new UnityWebRequest class.

Back to your code, keep in mind that there is a flow control function defined near the top on, the web service script, called ParseRequest. You need to add you new function there, in order for the script to understand your "UpdateACell" action.

 

Ah, my mistake is about ParseRequest Thanks a lot

 

Hi @Novack
All function I added to the Google Appscript are return 'undefined'. Yours are work well. Could you help me please? I added them to ParseRequest.

[code removed by hippo] - please don't post code that is part of a commercial asset, thanks!

 

Hello @NKidd,

I follow a general policy of not sharing parts of assets code here. For further technical details, we can follow up by email.

 

Hi @Novack
Really really sorry about my mistake. I will email you. Thanks!

 

I get the plugin work well on Android build, but do not on iOS build. Anyone get the same issue?
(On iOS, it show no error, just the wait time to long...)

 

Depending on your Unity version, it may be an issue that another GSFU user found: there is a problem with the WWW Unity class that creates a deadlock in the connection, and will prevent Google Sheets For Unity to work as expected.

There is a Unity temporary workaround discussed here, and the issue will hopefully be fixed soon (if not fixed already in the latest patch release).

 

Thanks @Novack
So in waiting time, I have to excute it manual after time...

 

FYI: Try this .mm file. Using it help me get GSFU run on iOS build (real phone tested)

The file is from Unity Tech guy @christophergoy
Source: http://forum.unity3d.com/threads/5-3-1p1-www-isdone-hangs-in-ios.377131/#post-2461328

 

Awesome man! Thank you very much. This will be quite useful for all the GSFU developers

 

Woot!
@christophergoy thanks for letting know man! The main issues related to the new UnityWebRequest class have been solved in the latest patch release also, so it looks like awesome news all around

 

Hi @Novack, I have an issue using GSFU in android (Im not tested on iOS yet).
I made a build with the sample scene "ConnTest", and installed into my device.
First time clicking "Update From Google Spreadsheet" button was fine, it respond in 3-5 sec.
Right after that I click "Update From Google Spreadsheet" button again, and it failed (Getting timeout).
I also tried increased the "maxWaitTime" variable to much longer(100sec), but it still same.

ps: In editor was fine, has no problem to keep requesting the data from google spreadsheet.
add-on: Unity version 5.3.4f1

 

Hi there @Spatanz, that seems new.

After it started failing on the device, did you checked from the editor again? Was still working? If it was, the problem is on the client side:

Have you checked everything was fine on the device? The internet connection was still up, that other apps could establish connections, and so forth? Have you tried restarting the device? Was the fail consistent, always hapening under the same conditions?

If all seems to be fine on the phone, you can try to poke another web service using WWW class, to start investigating where the issue is located. Please do keep me posted.

 

I figured out, its only happen on my android device (android 4.2.2), I tested on my friend device and its was fine.
I have no idea why my device always fail at second web service call (third time was fine).

BTW, thanks @Novack for the advice.

 

Glad you worked it out!

 

Oh right, now that some of the assets I was working on are done, and the bugs making things diffucult in Unity are fixed, Im planning on getting back to work on the Google Sheets for Unity 2.0

So far I plan on redesigning things a bit, and making use of some new Unity features.

• Moving to the new UnityWebRequest api, leaving behind the WWW class.
• Replacing LitJson with the -still rudimentary- Unity JsonUtility api.
• Implementing a small, simple and clean API for offering out of the box simple CRUD operations between Unity and Google Spreadsheets.
• Rewriting of the User Manual.

Any ideas or suggestions are welcome!

 

I just want to see the feature that Lysander was talking about, the ability to grab multiple sheets (or all of them), in a single request.