Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

[RELEASED] Unity-QuickSheet

Discussion in 'Assets and Asset Store' started by kimsama, Jan 5, 2015.

  1. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Unity-QuickSheet enables you to use google and excel spreadsheet data within Unity editor. With Unity-QuickSheet, you can retrieve data from a spreadsheet and save it as an asset file with a scriptableobject format even without writing single line of code.

    You can find Unity-QuickSheet on github:

    https://github.com/kimsama/Unity-QuickSheet

    Features
    • No need to write any single line of code.
    • It can retrieve data from excel file. (both of xls and xlsx format are supported.)
    • It can retrieve data from google spreadsheet.
    • No need to write a parser to retrieve data, it automatically serializes retrieved data into Unity3D's ScriptableObject, the binary format and so it is fast than to use XML which is usually ASCII format.
    New
    • Bunch of documentation are updated (2017.01.29). See that on GitBook site, can be found on here.
    • v.1.0.0.0 is released. See the release note.

    Say again, you don't need to write even single line of code!

    Enjoy!
     
    Last edited: Jan 29, 2017
    leni8ec likes this.
  2. V-Portela

    V-Portela

    Joined:
    Nov 7, 2013
    Posts:
    6
    Thanks! I needed something like this and working perfectly in Unity 5 :)
     
  3. jeromeWork

    jeromeWork

    Joined:
    Sep 1, 2015
    Posts:
    427
    This is a great. Thank you so much for sharing.

    Sorry to be such a newbie, but could you possibly provide a simple code example of how to retrieve data from the generated script files? It's not clear from your howto docs, why the four files are generated (2 in the Editor folder and 2 in the Runtime folder), or how and when to use each one.

    i.e. to populate a UI text object with data from my spreadsheet, which ScritableObject do I parse through...
    your-sheetpag-name.cs or your-sheetpage-nameData.cs ?
    And does this have to be done twice (the Runtime version and the Editor version, so that the data appears both at runtime and in the editor?)

    A simple example project would be very much appreciated!
     
  4. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Thank you for your interesting.

    I assume you've already know how to get data from excel and make it to an .asset file.

    Let me show you the very simple and easiest way to retrieve data from the extracted asset file. As you've mentioned, you have four generated files, two of them are under Editor folder and other of are any runtime folder you've specified in the setting.

    Let's say that you have Item worksheet in the excel file then you will get two generated script files, one is Item.cs and the other is ItemData.cs

    Code (CSharp):
    1.  
    2. public AssetLoader : MonoBehaviour
    3. {
    4.     // It's ScriptableObject derived class which is generated under any runtime folder you've specified in the setting.
    5.     // Drag .asset file onto 'item' member field from project view to Inspector view as any normal prefab file.
    6.     public Item item
    7.  
    8.     void Start()
    9.     {
    10.         // Retrieves the first row
    11.         ItemData itemData = item.dataArray[0];
    12.         Debug.LogFormat("Item Name: {0} Price: {1}", itemData.Name, itemData.Price);
    13.     }
    14. }
    15.  
    That's all. :)

    All of data are automatically loaded when AssetLoader class instance is created like the same way of what Unity does for a prefab. You don't need to write even a line of code to load .asset data.

    One more thing keep in mind that. Instead of directly accessing to the data as the above code snip, I highly recommend to use LINQ to query.

    Best,

    -Kim
     
  5. TheValar

    TheValar

    Joined:
    Nov 12, 2012
    Posts:
    760
    Looks cool! Any way to enter data for things like sprites or audio clips? I suppose you could just enter the Resource folder path where the asset is located.
     
  6. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Thanks and exactly you right. You need to just set a path for assets. The path can be either Resources or assetbundle's are possible.

    Cheers,

    -Kim
     
  7. hzq_hassan

    hzq_hassan

    Joined:
    Oct 12, 2013
    Posts:
    5
    Can I use this to post data to the google spreadsheet?
     
  8. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    No, it does not support to post data send back to google's spreadsheet.

    See the FAQ.
     
  9. hzq_hassan

    hzq_hassan

    Joined:
    Oct 12, 2013
    Posts:
    5
    Im not getting more than two columns of data from the spreadsheet, i created a test project and everything went fine. But it's not importing data of more than 2 columns, am I missing something?
     

    Attached Files:

  10. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Hmm... it strange. The spreadsheet looks like fine.

    Did you check that all settings are ok, every setting of types of the column?

    Is there any log on the console?
     
  11. hzq_hassan

    hzq_hassan

    Joined:
    Oct 12, 2013
    Posts:
    5
    I restarted unity and tried it again, with the same results. The logs are saying successfully generated.
     

    Attached Files:

  12. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Could you share the spreadsheet? So let me check it on my side.
     
  13. sfjohansson

    sfjohansson

    Joined:
    Mar 12, 2013
    Posts:
    369
    Thanks for making this.. :)
    I'm also testing this currently and getting missing cell content...also I can't see seem to get the array work. In the end I was hoping for that putting in text with line breaks into a cell would give me an array..something like "Sentence on 1, Sentence 2..." but don't know if that is supported behaviour?



    I have also tried now using comma to separate, which actually do give me an array...although as my intended content is a sentence... there can be commas in it.

    Cheers

    Stefan
     
    Last edited: Oct 31, 2016
  14. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    You should use ','(comma) to separate array elements not new-line delimiter.

    Modify the cell:
    Text Line 1, Text Line2

    By the way it looks so strange of missing 'Field_ID' column. Remove any new-line break (if anything is there) and download it again.
     
  15. sfjohansson

    sfjohansson

    Joined:
    Mar 12, 2013
    Posts:
    369
    Yeah just noticed that...as I got it to work..but yeah in my case I have to use commas I wont be able to use this..unless I can modify it to support multiline text fields. Will try cleaning up and see if it populates all the fields..
     
  16. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    If you want to new-line delimiter instead of comma, you need to modify code for yourself.

    See the Assets\QuickSheet\GDataPlugin\Editor\GDataDB\GDataDB\Impl\Serializer.cs file and find Deserialze function.

    Code (csharp):
    1.  
    2.         public T Deserialize(ListEntry e) {
    3.             var t = typeof (T);
    4.                     ...                  
    5.              try
    6.              {
    7.                 if (property.PropertyType.IsArray)
    8.                 {
    9.                     const char DELIMETER = ','; // '\n' // HERE!, changing ',' to '\n' is what you want.
    10.                      ...
    11.  
    Note that change of the above only valid on google spreadsheet not for excel file.

    Cheers,
     
  17. sfjohansson

    sfjohansson

    Joined:
    Mar 12, 2013
    Posts:
    369
    Ahh cheers..there it is :) So he spreadsheet was clean..it was just the cell alignment on the other cells..tried changing the delimiter

    "const char DELIMETER = '\n';

    but no luck...maybe might line ending type issues here..I'm running on mac..and then I guess there is how it is treated on the google side.. =O
     
  18. sfjohansson

    sfjohansson

    Joined:
    Mar 12, 2013
    Posts:
    369
    ..and we have lift off.. :)

    Got it working when I bypassed the processing of the cell content string a few lines down:
    Code (CSharp):
    1.  
    2.                                 // remove whitespace between each of element
    3.                                 string str = c.Value.ToString();//new string(c.Value.ToCharArray()
    4. //                                                        .Where(ch => !Char.IsWhiteSpace(ch))
    5. //                                                        .ToArray());
    6.  
    7.                                 // remove ',', if it is found at the end.
    8.                                 char[] charToTrim = { ',', ' ' };
    9.                                 str = str.TrimEnd(charToTrim);
    10.  
    11.                                 // split by ','
    12.                                 object[] temp = str.Split(DELIMETER);
    13.  
    14.                                 Array array = (Array)Activator.CreateInstance(property.PropertyType, temp.Length);
    15.  
    16.                                 for (int i = 0; i < array.Length; i++)
    17.                                 {
    18.                                     array.SetValue(Convert.ChangeType(temp[i], property.PropertyType.GetElementType()), i);
    19.                                 }
    20.  
    21.                                 property.SetValue(r, array, null);
    So now the issue with missing field content is the only hurdle...
     
  19. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Oh, you're working on Mac machine.

    On OSX, not likely on Windows, you should check either carriage-return, '\r' after '\n'

    So the line should be changed like the following:
    Code (csharp):
    1.  
    2. const char DELIMETER = '\n\r';
    3.  
    Cheers,

    ps. Some others prefer to use newline-breaker instead of using comma for array type. It may be better to set delimiter on somewhere in the configure file so the user determine and set the delimiter for their own. I'll consider it later.
     
  20. sfjohansson

    sfjohansson

    Joined:
    Mar 12, 2013
    Posts:
    369
    Yeah it would be nice if it would be possible to choose delimiter for each field, that would go nicely into the inspector where one declares type and if it is an array. :)

    As the multiline fields work..there is only the issue with the missing content in the "Field_ID" field...any ideas what could be causing this?

    It could be the same issue as the previous user has...as I've used it successfully with a table with only two columns...and this one has more than 2.. =O
     
  21. sfjohansson

    sfjohansson

    Joined:
    Mar 12, 2013
    Posts:
    369
    I did manage to track down why it didn't work...by inserting some debug stuff into the deserialise function...failed due to the underscore in Field_ID... it was attempting to fetch it as "fieldid"

    Looking at hzq_hassan ,he also used a column with an underscore Stuff_1, Stuff_2
     
  22. hzq_hassan

    hzq_hassan

    Joined:
    Oct 12, 2013
    Posts:
    5
    sfjohansson That did it, Thanks for the help and Thank you kimsama for making this, it's awesome;)
     
  23. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Good to here you guys solved the problems. :)

    Cheers,
     
  24. dasbin

    dasbin

    Joined:
    Jan 14, 2012
    Posts:
    261
    Is there any way to modify the way arrays work so they can support Enums?
    Was getting all kinds of Deserialization errors; apparently the cause was commented in the code:
    " //NOTE:enumarraytypeisnotsupported.(Doesitreallyneeded?)"
    In my case, it is needed.
     
  25. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Hey,

    What do you use for a spreadsheet, excel or google spreadsheet?

    Check the issue:

    https://github.com/kimsama/Unity-QuickSheet/issues/16

    There were already that issue and unity-quicksheet supports array of enum type on google side but not yet for excel.

    So, there are options. Do it for yourself or wait till it will be done. :)

    I guess it should not too much difficult done on excel query code.

    Cheers,
     
  26. snumryk

    snumryk

    Joined:
    Dec 30, 2016
    Posts:
    2
    Hi, sorry for the nub question but i got a problem with adding .asset file to my project. I created Quests.asset file with
    Unity-QuickSheet and then just copied it into my project Assets folder. When i click on this file in my project inspector I can see "the associated script can no be loaded" message. When i create "public Quests quests;" in my script all i get is "type not found" error. Am I doing something wrong?
     
  27. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Hey,

    Could you tell me more details about the problem?

    - What type of the spreadsheet you used, excel or google's?
    - Let's me see the spreadsheet what you've created or share it if you can
    - Tell me what Unity version you used.

    And I'm rewriting docs and you can find the it on the gitbook page:

    https://kimsama.gitbooks.io/unity-quicksheet/content/

    It may help you solve the problem.


    Best regards,

    -Kim
     
  28. snumryk

    snumryk

    Joined:
    Dec 30, 2016
    Posts:
    2
    Ok, got it. There was a machine generated C# scripts in Runtime directory(Quests.cs and Quests.Data.cs in my case). When I copied them to the new project, all started to work.
     
  29. PanicEnsues

    PanicEnsues

    Joined:
    Jul 17, 2014
    Posts:
    187
    Hi Kim,

    I'm trying to import a .xlsx file; the setup and import process work fine, but after I Generate the script files, the "*Editor.cs" script errors with "The type or namespace name `BaseExcelEditor' could not be found."

    For install, I just put the QuickSheet folder under Assets; do I need to move something into Plugins?

    Regards,

    -Scott
     
    Last edited: Apr 29, 2017
    kash1103 likes this.
  30. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Check the settings first, especially the folder where you set to put the generated script files. You should not set the 'Plugins' folder for "Runtime" and "Editor" folder on the QuickSheet's importer setting.

    Secondly, you don't need to set the folder of 'QuickSheet' to be under the 'Plugins' folder.

    Cheers,

    -Kim
     
  31. JuanMaldonado

    JuanMaldonado

    Joined:
    Oct 12, 2012
    Posts:
    30
    Hi @kimsama ,

    I'm using an excel spreadsheet and I need to update the scriptable object data at runtime when my app initializes. Is this possible?

    The thing is I need to download and replace the sheet with an updated file from the web using the WWW class. The sheet needs to be located in the Streaming Assets folder and when replaced by the new downloaded version, I need the scriptable object to update the data. Is there a way to toggle the Update button of the scriptable object at runtime or any other way?

    Cheers,
     
  32. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    The answer is not. Unity-Quicksheet cannot update on runtime.

    You may need other options for that purpose.


    Best,

    -Kim
     
  33. idurvesh

    idurvesh

    Joined:
    Jun 9, 2014
    Posts:
    495
    Hi, after upgrading from Unity 5.4 to 5.5 I am getting this error for QuickSheet asset,


    How can I solve it?
     
  34. kimsama

    kimsama

    Joined:
    Jan 2, 2009
    Posts:
    166
    Hey,

    Where did you get Unity-Quicksheet from?

    I highly recommend to get the latest one from its git repository.

    See the below link, the problem you've get has already fixed issue:

    https://github.com/kimsama/Unity-QuickSheet/issues/34

    Now, there is no 'nunit.framework.dll' under the 'GDataPlugin/Editor/Google' folder.

    Simply removing that file will fix the issue you have but check it again you've get the latest fresh source files from github repository.

    Feel free to drop me a note if you have still problem or any other issue.

    Cheers,

    -Kim
     
  35. protomelvin

    protomelvin

    Joined:
    Mar 28, 2013
    Posts:
    1
    Hi, this is a great tool and I'm liking it so far. Just want to know if there's a way for me to hit download on multiple scriptable objects at once or to hit the download function from a different editor window.

    I'm trying to make an editor for my project where I'll have many sheets I'm pulling data from and instead of updating them one by one, I'd like to update them all at once in this new editor. I just can't figure out how to access the download function that's on the scriptable object from my new editor window. Any advice would be greatly appreciated.
     
  36. pravinyadav

    pravinyadav

    Joined:
    Jan 24, 2010
    Posts:
    30
    I want to place Excel file in StreamingAssets folder. I have successfully generated Import settings but after reimport it is giving following error
    NullReferenceException
    Sheet1AssetPostprocessor.OnPostprocessAllAssets (System.String[] importedAssets, System.String[] deletedAssets, System.String[] movedAssets, System.String[] movedFromAssetPaths) (at Assets/CC_PravinYadav/Excel/Editor/Sheet1AssetPostProcessor.cs:42)
     
  37. gareth_untether

    gareth_untether

    Joined:
    Jan 5, 2018
    Posts:
    69
    How do I import a column with items that have spaces and keep the spaces? When I import now all the items have their spaces removed from the names.
     
  38. gareth_untether

    gareth_untether

    Joined:
    Jan 5, 2018
    Posts:
    69
    Found the answer in ConvertExt.cs.

    Removed:

    Code (CSharp):
    1.             // remove whitespace between each of element
    2.             str = new string(str.ToCharArray()
    3.                                 .Where(ch => !Char.IsWhiteSpace(ch))
    4.                                 .ToArray());
     
  39. maxklimenok

    maxklimenok

    Joined:
    Feb 14, 2017
    Posts:
    2
    Hey Kim @kimsama ,

    I have just updated my project to Unity 2020.1.0a23 and have got the following error:

    Assembly 'Assets/Plugins/QuickSheet/GDataPlugin/Editor/Google/Google.GData.Client.dll' will not be loaded due to errors:
    Google.GData.Client references strong named Newtonsoft.Json, versions has to match. Assembly references: 4.0.5.0 Found in project: 6.0.0.0.

    So it seams that the reference and or an assembly has to be updated in the source code to a newer version.
    And thanks for the great asset!
     
  40. rerwandi

    rerwandi

    Joined:
    Dec 8, 2014
    Posts:
    544
    We cant combine enum with array?
     
  41. bobozo

    bobozo

    Joined:
    Jul 7, 2015
    Posts:
    1
    I have updated my project to Unity 2019.3.11 -> 2019.4.0f1

    Don't work Download.
    BaseGoogleEditor<DB>
    {
    ...
    EditorUtitlity.SetDirty(targetData); // <-- Don't work.
    AssetDatabase.SaveAssets();
    }
     
    Last edited: Jun 15, 2020
  42. wwaero

    wwaero

    Joined:
    Feb 18, 2020
    Posts:
    42
    @kimsama I'm getting an error that no template file is found when generating, doesn't seem like this plugin currently works.
     
  43. robin-m3d

    robin-m3d

    Joined:
    Dec 15, 2020
    Posts:
    3
    Hey @kimsama, great work!!!

    I really like your quicksheet project. I have successfully implemented it, for instantiating objects, that were defined in the excel sheet beforehand. Now I am wondering whether it is possible to change the Excel file while the Unity build (.exe) is still running. And save it, and finally reimport such that the data is changed in the scriptable object too. Like that I could import a changed data set on runtime :) Do you think this might work or do you have any hints, on how to do it?

    Thanks
     
  44. bravo075

    bravo075

    Joined:
    Dec 7, 2012
    Posts:
    32
    Sadly it seems that this plugin does not work anymore because Google has deprecated the old Google Sheets API :/
    Has anyone been able to make it work with sheets v4?
     
  45. bravo075

    bravo075

    Joined:
    Dec 7, 2012
    Posts:
    32
    Alright, for anyone interested I managed to get it working with google sheets v4, but none of the current query classes will work with this, I basically had to rewrite the whole authentication and data import, if anyone wants to use this my suggestion is to look for another one or write your own.
     
    doubledope and no-weekend like this.
  46. no-weekend

    no-weekend

    Joined:
    Jun 7, 2013
    Posts:
    12
    Gah, I have serval project using this and we just hit this issue. I am now wondering it it's going to be easier to roll my own solution, fix this or find another premade one.
    Since quick sheets hasn't been updated in a while I'm guessing it's time to jump off this sinking ship.
    I will miss it.
     
  47. doubledope

    doubledope

    Joined:
    Jan 8, 2014
    Posts:
    4
    Hey! Do you think it's possible to open a PR for your fixes on the repo? That way it might live on.