Realtime Unity Data with Google Sheets | Third Aurora Augmented Reality Tech Company

all right guys today we're talking about real-time data with google sheets and unity together today we're going to be going through how to take a sheet in google sheets and utilize that information into a unity application now there are so many different use cases for this things like including translating information including getting stock information from google sheets own calculations and then bringing that in to your own unity project where you use that in community itself look there are so many different possibilities it could just be straight up showing the information to screen which is what we're going to do today as a basic sort of output but you know you could bring this into augmented reality you could bring this into your gaming engine like there are just so many different options to once you have this ability to take real-time data how you use it is then up to you so whether it's weather it's player information like there are so many different options anyway i want to give the groundwork behind actually getting that information figuring out how you configure your google sheet and then how you bring that into unity and utilize that information so let's get started with the google sheets side okay so let's talk about google sheets to start with now google sheets we're going to utilize to store information that we want to then send into unity now this could be to whatever level you want in this version of things we're going to be utilizing just one sheet that is a table of information and that is only because it is the simplest way and form of actually bringing stuff in so let's say that we have um some products so we've got the name of the product the quantity in stock and the price and so what we're going to be doing for this is we're going to say um i've got a balloon for sale i've got a map for sale and i've got an umbrella for sale and we're going to say that we have 50 balloons we have 25 maps and we've got 10 umbrellas and basically all we're doing here is setting up a data structure like a database would where we can then write uh this is ten dollars oh wait these are balloons balloons for ten dollars maybe a bit excessive let's say like two dollars four dollars and ten dollars and so we're going to leave it in that format i'm not going to touch any formatting in google sheets itself that is the the base structure we're going to utilize you could also utilize uh other pieces like price usd and then we could look up a google function which is google finance which you can then utilize to uh convert the information to a different currency okay and so i've utilized the google finance calculation uh where we utilize the currency aud to usd and i'm just timesing it by the australian dollar so basically you've got the australian dollar price here and then the usd price which i'll just i'm just going to rename this now for price usd so what we can do is we can specify all our information in google sheets here i'm going to call this spreadsheet just uh testing real time sorry testing real time data i just i have a name that suits for this and what we need to do with information with this information with this document itself is take this base document here and then we're going to effectively publish this for anyone on the web to access so it does become a public document so anyone can theoretically access it so firstly we're going to go to file and then we're going to go publish to web and what we're going to be utilizing is uh to publish this entire document we want this this first sheet exported as a web page and we don't need to restrict any of this information like we could restrict access to only certain people but that's going to make it quite difficult to interact in the way we're going to so we're going to leave everything else just default and we're going to set the the sheet to the one that we're developing on and we're going to utilize the web page so hit publish and we want to hit ok and so this gives us a link to if we just copy and paste it in we can see the real-time data there so if if we change something like the number of balloons now to be 49 and we refresh this page we should see in a moment that information refresh obviously there's a slight delay there and that's because it has to effectively republish that document to the web so once we've done that we now have access to our entire information from anywhere and this exclu this is including unity and what we're going to be doing but it is also from any application so utilizing that publish function is quite a good one now with that publish feature what we need to do is now access a specific url so i'm just going to bring up a notepad just so i can easily show this url um but it is to sorry i'm going to just set that back to this version so we can enlarge the text size what we have here is spreadsheets.google.com forward slash feeds forward slash list we need to get our specific id to that document and forward slash od6 public values question mark alt equals json and this is going to allow us to take this spreadsheet document and then return it in adjacent format so firstly let's get the get the id which is sorry which is this url piece here and we're going to bring that in so we're going to replace the id here paste that in copy the full url and if we enter that one into our browser now we can see that we get this json list and in here we have a few different pieces of information but we have like our version number our encoding um some feed information which a lot of this um we don't need so i'm just gonna collapse all of these but what we do need is this entry section so each entry in here so this is an array of entry there's an array of objects in here which each object holds again a lot of information that we don't need an id updated category uh we have some now text in here which is like the balloon the the actual then content for that balloon itself and in here we have these gsx dollar name dollar quantity dollar price dollar price usd with each individual value and this is the information that we're looking for so i'm also going to just collapse these ones but these four entries here relate to the four entries in each row so we have an array of objects and each object is the specific row in the document so the first one we can see is balloon second one if we scroll down we can see this map third one we can see is umbrella and so this is the basics to well here's the information we need to get from this json file how do we actually now do that in unity itself so let's jump across the unity and get things going there [Music] okay so i've gone ahead and created a blank uh unity project here just so we can get things cracking on just a don't need to worry about any pre-existing stuff so first off what would you want to make it do when we actually run the project like do we want to show text on the screen do we want to manipulate link in the code well right now what we're going to do is we're going to set up a basic text element so we're going to go ui and text and we're just going to have this text element here is going to be utilized as a way to project the information out to the screen so i'm just going to make the width 400 the height 400 just to you know try to prevent any information from not showing out to the screen and my goal is to have something along the lines of uh having like a balloon come out and then dash um 50 left and then uh dollar two dollars and then in brackets uh whatever the american price was which i think it was a dollar forty seven and so that's what i want my output to look like so i would have that for each entry then underneath so we would see something like that but obviously with each entry's own information for now i'm just going to leave this as empty just so we know when the script is or isn't working so i'm not getting confused on that perspective now along with this uh we need to we need to set up some scripts in order to call to the server to obtain the information before we do that what we're going to build is just a simple game object that controls running the script so i'm just going to have a game an empty game object here called scripts and this will just allow me to assign whatever script we build to that game object so that runs on start and runs on updates of frames and likes so that's the the real simple basic build to unity itself let's now go ahead and start getting our scripting site ready to go so what we need to do now is utilize unity's json utility in order to take that giant long string and bring it into different levels of objects now before we do that i want to take you through why we can't do that so typically when you utilize json utility it would be setting up different c-sharp classes that effectively have different levels so normally you would have firstly i'll just i'll just do this at the bottom this is not going to be true code but we would have like a public class which is the sheet entry and so that was the values all that we just saw then which in that you might have a public string which is the version and also the encoding and then also what was our other item if we just look back here so we have version encoding and feed and so you might try and associate all those into different variables here and in fact the feed variable here would normally become another public class that we're entering so if we call this sheet feed and we set up another one just underneath it again just for showing this off as an example so then in the feed class well we would need to write out all the entries that are in this so we've got like xml ns and again and again and there's many different levels there now fortunately you don't need to specify every single item you can only you can choose to only specify the ones that you want to utilize so in our case we don't we don't actually need anything here other than the entry variable so let's say we then defined another piece which is public string entry and sorry i mis misrated this so we're saying that the sheet entry is the top level document inside that sheet entry well actually we only need the feed and the feed up is made up of an object called sheet feed and inside that we also only need uh the entry now this is going to get confusing because i call this entry so i'm going to call it item so the overall sheet is called sheet item then inside that is a variable called feed which then is going to be specified as sheet feed and then inside that we have the entry now the entries themselves if we go back to the item here is an array of different objects but in these objects there's only a few different items that we need which is like the gsx name and inside that is the letter t and where this where where this all leading towards is um a giant issue that i ran into when trying to create this project for just as this for this example so inside this we're going to have that each sheet feed has a sheet entry and inside that entry we need to get the gsx name as one example which we'll call public sheet public sheets name and that can be the variable name and this is where we start to run into issues so we would have public class sheet name and inside that we'll have a dollar or a public string which is dollar t and and this is where we start running into issues so we have gsx dollar name and then inside that has a variable an object called dollar t and in theory this is typically how you would normally structure this process if you're utilizing the json from the json utility from unity by default unfortunately inside it it doesn't like or doesn't expect to see dollar characters and the dollar t is an issue the dollar name at the end of the gsx is an issue which means we can't utilize the core json utility that unity offers so what we do need to utilize is an alternative version that is still supported in unity and i try to tend to move a wave or try to not look at bringing in external sources we need to utilize the simple json parser in order to use a a json object as just a standard object in unity itself so what we need to firstly do is download the framework which i just utilized the github link here on the wiki and sorry to to get to this um it is it is simple.json uh it was my fault i was misreading it into the search uh so if we search for unity simple.json and then it's this first entry the wiki.unity3d.com and then index.php simple.json and inside this we want to use the github link and from here all i've done is i've hit download on this code repository so i'm just going to download the zip because i'm copy bothered waiting for all the time to set up to to pull anyway uh we're then extracting that so i'm just going to open it and it will it'll extract the results and so in here what we're utilizing is where we're effectively needing to take all this information copy it and put it into our scripts area so if we go back into unity itself go into our project view in the scripts folder here we want to drag and drop that information in here and that's going to allow us to use it so on the wiki it does specify to create a plugins folder uh trust me i tried that did not work create a scripts folder and it will work and i i don't know why don't i don't yeah i can't answer i can't answer why that works uh it just does anyway we're going to delete all that information because we no longer require that what we're then going to be doing is we're going to be setting up the json pass to to take the adjacent object and turn it into an object so uh jason string goes into the json.pass and we put it into a variable called o now in order to use the uh simple json parser we also need to specify at the top here using json simple.json uh to allow for this uh to actually work so once we've passed the json uh what we have is then an object that contains all the information in it as we require now the only complexity comes here where we need to iterate through each row so what we need to do is run through a for each loop uh so that we can take each value so in this we're going to be running for each variable item so we're going to create a new variable called item in o which is our object here and then in brackets you use brackets in this format not dots because it's technically an array is effectively how i look at it and we're going through each feed entry item to then create this variable called item and so this item then has if we go back to our adjacent here each of these items then is effectively these entries so if we collapse this entry here we can see these are all the pieces inside this and what we actually receive in this for each loop is effectively a string and so what we're then wanting to do is we're going to re-pass the jason the string that we've received back so what we're going to create is a sub item or item mo in this case uh sorry item oh object version of the item and we're going to pay throw it through that json.pass item again and so what we get out of this after running through all this is the ability to take item o and then say gsx dollar name dollar t and that gives us the name object as an example so the first one it's like umbrella but this eventually now has given us the ability to access this now the only weird thing that happens here is when we pass this into the json item actually turns it into an array so we actually have to write the first object in that item array get this value for it so that is now the way that we're able to access the each of the individual elements inside which is pretty awesome because it took me a very long time to get to that point so what i'm going to do now is we're going to build a string which is going to be called like update text and we'll set it to empty at the start here and what it's going to do is it's basically going to go through each entry and we're going to add information to the string so i'm going to do real basic here so we've got update text plus equals idemo the name first and then we're gonna add uh like the column and then we're gonna add the quantity so this would be instead of dollar name it now becomes quantity and then plus and left because we wanted to say like 50 left or 49 left and then we've got the pipe and then we're going plus and we want to send in the price and then we want to send in brackets uh the american price and obviously it's a very long string and i'm just simply concatenating all the pieces together it's not particularly the best way to do it but it's going to work for our example and so this is then going to take uh sorry and then i want a new line at the end of that so that basically goes through creates that string creates a new line creates the rest of string in that same variable and so what we want to do then is at the end we want to have um a text element here that we're calling so that we can then update so as part of this public class we're going to set a public text element which is called output area and for the output area at the very end we're going to update it to be the update text assuming that we didn't get an error so output area dot text equals update text and what we need to do back in unity now is we need to specify that text element so this text element is the one that we want to update so in scripts uh we should be seeing the text although we aren't this needs to be some sort of error oh and that's my fault so text is unknown right now uh and that's my fault my apologies uh we need to include unity engine dot ui into our using so that we can specify text as an actual thing so now going back into unity uh if we look at our scripts here we now have this text to specify so i'm going to drag drop the text on and and i'm in play mode i didn't even realize it was in play mode that's my fault let's do that again we'll drag and drop the text and so now we should be at a point where we have a working state so if we hit play we should now see this word empty get updated with our sheets google sheets information which is pretty awesome okay so with that now in play let's go back to our google sheets so i'm gonna go back into the sheets.google i'm gonna go into our document testing real time and so in here we can see our balloon map umbrella if i change the balloon quantity to 49 and we go back to unity you'll see that it still says 50 here and if we stop and hit play again it should now say 49 which it does so what would be kind of cool is if that could update in real time rather than having to start and stop so what we're going to do is in visual studio we're going to run this co-routine on every update now maybe this is not a great use of resources in the long run for this example it works perfectly so we're going to go back to unity now we're going to hit play and we may even split screen here just so you can see it happening at the same time but let's say the quantity is now 50 and we go back and it says 50 instantly and in fact i am going to split screen this so let's drag these screens in a bit so we can see between them so if we now go into our area here we hit 49 and we can see that update to 49 we want to change the price to four dollars well we can see the price is now four dollars in here if we want to change it from a balloon to a pencil at four dollars we can it now says pencil 49 if we wanted to add in another piece like a descriptor uh this is for my and in this i'm just going to i'm going to turn this into a calculation just so i don't have to copy and paste this this is for my ampersand a a2 and we're just going to copy and paste that down now that didn't automatically come in and that's to be expected because we're not doing any sort of uh automatic pulls of the information so let's just pause that momentarily and go back to visual studio and build that in so if we ever add a column into our area what we do need to do is build that into the output formatting so i'm going to add uh into this that after we've outputted the name we're going to add in that descriptor in brackets next to it so we're going to have a plus and then we're going to have an open bracket and then we're going to add our descriptor content close the brackets and do a double dots and so what we need to set here then is our new variable which we call descriptor hit save and if we go back to unity and i'll open up google sheets at the same time again if we hit play we should now see pencil this is for my pencil map this is for my map umbrella this is for my umbrella i did not leave a space unfortunately that was my fault so it looks a bit squished in unity there so there we go we added a little space and that looks much nicer now so we'd say 50 and we could change this uh back to balloon oops and we could change the price to a dollar and we can see that reflect through in the other side so it's just worth noting that whilst it is just taking whatever format or whatever content you put into uh google sheets here it does still work through calculations so it does still take into account that we did a calculation on that price usd and also the descriptor and we'll bring that into unity with that information as the endpoint but that's it that is uh our quick run through of taking google sheets and bringing it into unity may seem simple but it was a real pain to bring that to figure out the solution behind bringing in those values where there was a dollar at start it was uh a lot of time spent on that but anyway that is uh yeah it for the real time showing updates in unity [Music] and so that's it guys we've gone through we've taken out google sheet we've brought it into unity and we're currently just displaying the information out the screen but there are so many different possibilities that you can take this application and utilize it in whatever way you want guys if you have any ideas for other videos please drop them down in the comments it'd be much appreciated we're happy to try and take on anything that we can to help solve an issue for you guys until next time have a great day peace

Channel: Third Aurora

Views: 40,233

Keywords: augmented reality, artificial intelligence, mixed reality, technology, google sheets, simpleJSON, unity, realtime data

Id: zgSES5PQNu8

Length: 26min 11sec (1571 seconds)

Published: Mon Jul 12 2021