Guild Roster Dumper to Google Sheets

Mathius_Mordred
Mathius_Mordred
✭✭✭✭✭
Hi All

I wanted an addon to output our guild roster to a Google Sheet. The only add-on I could find was last updated nearly 5 years ago, and it no longer works. I found that using Arkadius Trade Tools I could export all data but this is in LUA format. So I put together a sheet that strips out the unwanted stuff and ends up with a list of all your members.

To use it, first go to Exports in ATT, select the check box for members only (although this doesn't seem to work), export then reload UI to write to disk.

In your documents folder you will find this file (Path will be something like: C:\Users\sales\OneDrive\Documents\Elder Scrolls Online\live\SavedVariables)

s7tp6qlxz3t7.jpg

Open this with Notepad.

Then go to this link to get the spreadsheet and make a copy:
https://docs.google.com/spreadsheets/d/1JoHGwo-L8nJaeT4LgEncFmHHc4OR-E0_vRjBvOpzsy0/edit?usp=sharing

In the Notepad, select all the text then go to the spreadsheet and past in cell A3

You will then have your entire guild listed in alphabetical order.

Feel free to improve on this or suggest improvements

uw0zm0hjpm7p.jpg

Edited by ZOS_GregoryV on 4 October 2024 01:11
Skyrim Red Shirts. Join us at https://skyrimredshirts.co.ukJoin Skyrim Red Shirts. Free trader. We welcome all, from new players to Vets. A mature drama-free social group enjoying PVE questing, PvP, Dungeons, trials and arenas. Web, FB Group & Discord. Guild Hall, trial dummy, crafting, transmutation, banker & merchant. You may invite your friends. No requirements
  • N00BxV1
    N00BxV1
    ✭✭✭✭✭
    I'm gonna show how to make an AddOn that can get a list of your guilds and members, and then how to strip all of the Lua from the SaveVariable's text using Regular Expression in Notepad++...

    Create a new folder (Right-click, New Folder) in the AddOns folder called "MyGuilds" (Elder Scrolls Online\live\AddOns\MyGuilds).

    Inside the "MyGuilds" folder create a new file (Right-click, New Text Document) named "MyGuilds.txt" (make sure file extensions are enabled in Windows).

    Open "MyGuilds.txt" with Notepad, copy/paste the text below into it, and then save the file:
    ## Title: MyGuilds
    ## Description: This is my guilds addon.
    ## Author: N00BxV1
    ## Version: 1.0.0
    ## APIVersion: 100044
    ## SavedVariables: MyGuilds_Vars
    MyGuilds.lua
    

    Inside the "MyGuilds" folder create another new file (Right-Click, New Text Document) named "MyGuilds.lua" (make sure file extensions are enabled in Windows).

    Open "MyGuilds.lua" with Notepad, copy/paste the text below into it, and then save the file:
    EVENT_MANAGER:RegisterForEvent("MyGuilds", EVENT_ADD_ON_LOADED, function(eventCode, addonName)
        if addonName ~= "MyGuilds" then return end
        EVENT_MANAGER:UnregisterForEvent("MyGuilds", EVENT_ADD_ON_LOADED)
        -- MyGuilds_Vars is defined in MyGuilds.txt
        MyGuilds_Vars = nil
        MyGuilds_Vars = {}
        for guildIndex = 1, GetNumGuilds() do
            local guildId = GetGuildId(guildIndex)
            local guildName = GetGuildName(guildId)
            MyGuilds_Vars[guildIndex] = {}
            MyGuilds_Vars[guildIndex][guildName] = {}
            for memberIndex = 1, GetNumGuildMembers(guildId) do
                local memberName, _, _, _, _ = GetGuildMemberInfo(guildId, memberIndex)
                MyGuilds_Vars[guildIndex][guildName][memberIndex] = memberName
            end
        end
        for guildIndex, guildTable in pairs(MyGuilds_Vars) do
            for guildName, memberTable in pairs(guildTable) do
                table.sort(memberTable)
            end
        end
    end)
    

    You should now have a folder named "MyGuilds" inside the AddOns folder. And inside of the "MyGuilds" folder there should be two files, one named "MyGuilds.txt" and the other named "MyGuilds.lua".

    Now start the game and login to a character... When your character has fully loaded into the game then type this into chat and press enter:
    /reloadui
    

    The SavedVariables should now be saved to disk. Now quit the game so that we can do the next part...

    Go download Notepad++ if you don't already have it. It's better than Windows Notepad and we need it because it can use Regular Expression.

    Now go to the SavedVariables folder and open the file named "MyGuilds.lua" in Notepad++ (Elder Scrolls Online\live\SavedVariables\MyGuilds.lua).

    The text should look something like this:
    MyGuilds_Vars =
    {
        [1] = 
        {
            ["First Guild Name"] = 
            {
                [1] = "@UserNameA",
                [2] = "@UserNameB",
                [3] = "@UserNameC",
                [4] = "@UserNameD",
                [5] = "@UserNameE",
            },
        },
        [2] = 
        {
            ["Second Guild Name"] = 
            {
                [1] = "@UserNameF",
                [2] = "@UserNameG",
                [3] = "@UserNameH",
                [4] = "@UserNameI",
                [5] = "@UserNameJ",
            },
        },
        [3] = 
        {
            ["Third Guild Name"] = 
            {
                [1] = "@UserNameK",
                [2] = "@UserNameL",
                [3] = "@UserNameM",
                [4] = "@UserNameN",
                [5] = "@UserNameO",
            },
        },
        [4] = 
        {
            ["Fourth Guild Name"] = 
            {
                [1] = "@UserNameP",
                [2] = "@UserNameQ",
                [3] = "@UserNameR",
                [4] = "@UserNameS",
                [5] = "@UserNameT",
            },
        },
        [5] = 
        {
            ["Fifth Guild Name"] = 
            {
                [1] = "@UserNameU",
                [2] = "@UserNameV",
                [3] = "@UserNameW",
                [4] = "@UserNameX",
                [5] = "@UserNameY",
            },
        },
    }
    

    Press Ctrl+A in Notepad++ to Select All text.

    Press Ctrl+H in Notepad++ to open the Find/Replace window.

    In the Replace window under "Search Mode" select "Regular expression". Leave all other options unchecked.

    In the Replace window's "Find what" box enter this text:
    ".+?"(*SKIP)(*F)|.
    

    In the Replace window's "Replace with" box leave it empty.

    Now click "Replace All" and that should remove all of the Lua parts of the text.

    In the Replace window's "Find what" box enter this text:
    \r\n\r\n\r\n
    

    In the Replace window's "Replace with" box leave it empty.

    Now click "Replace All" and that should remove a bunch of empty lines.

    In the Replace window's "Find what" box enter this text:
    "
    

    In the Replace window's "Replace with" box leave it empty.

    Now click "Replace All" and that should remove all of the quotation marks.

    Finally you're done and should have some text that looks like this:
    First Guild Name
    
    @UserNameA
    @UserNameB
    @UserNameC
    @UserNameD
    @UserNameE
    
    Second Guild Name
    
    @UserNameF
    @UserNameG
    @UserNameH
    @UserNameI
    @UserNameJ
    
    Third Guild Name
    
    @UserNameK
    @UserNameL
    @UserNameM
    @UserNameN
    @UserNameO
    
    Fourth Guild Name
    
    @UserNameP
    @UserNameQ
    @UserNameR
    @UserNameS
    @UserNameT
    
    Fifth Guild Name
    
    @UserNameU
    @UserNameV
    @UserNameW
    @UserNameX
    @UserNameY
    
    

    :D

    P.S: Don't mind the weird coloring of the text in the Code blocks, that's just how it works on these forums...

    Edited by N00BxV1 on 29 September 2024 13:57
  • Baertram
    Baertram
    ✭✭✭✭✭
    -HInt-

    If your addons folder looks liek this you are in trouble very often:

    C:\Users\sales\OneDrive\Documents\Elder Scrolls Online\live\AddOns

    If you use OneDrive to backup your documents folder ESO very often got sync problems and overwrites your addons from the cloud, if you update them via Minion or manually, and thus you end with corrupt or old addon files!
    Also corrupts yoru SavedVariables in many cases -> got so many reports meanwhile and we tracked that down to being OneDrive by MicroSoft.

    Better disable the sync "called backup in OneDrive" of your Documents folder in OneDrive if you want to make sure ESO works properly.

    Here is a link at the ESOUI forums about that, troubleshooting Minion:
    https://www.esoui.com/forums/showthread.php?t=8082
    Check point 8, it got a link where and how to turn of that backup of your documents in OneDrive.


    It will disable the sync to cloud of your documents folder so make sure you either manually copy the folder to OneDrive (somewhere else) if you need the data in your cloud (seriously that documents folder contains so many personal data I would not recommand syncing this to a cloud, but that's up to you guys...) or keep it enabled and if you got any problems with ESO addons or SavedVariables disable it then. Reenabling though might get you into trouble with ES again afterwards, so be warned.
    Edited by Baertram on 28 September 2024 14:14
  • Mathius_Mordred
    Mathius_Mordred
    ✭✭✭✭✭
    Baertram wrote: »
    -HInt-

    If your addons folder looks liek this you are in trouble very often:

    C:\Users\sales\OneDrive\Documents\Elder Scrolls Online\live\AddOns

    If you use OneDrive to backup your documents folder ESO very often got sync problems and overwrites your addons from the cloud, if you update them via Minion or manually, and thus you end with corrupt or old addon files!
    Also corrupts yoru SavedVariables in many cases -> got so many reports meanwhile and we tracked that down to being OneDrive by MicroSoft.

    Better disable the sync "called backup in OneDrive" of your Documents folder in OneDrive if you want to make sure ESO works properly.

    Here is a link at the ESOUI forums about that, troubleshooting Minion:
    https://www.esoui.com/forums/showthread.php?t=8082
    Check point 8, it got a link where and how to turn of that backup of your documents in OneDrive.


    It will disable the sync to cloud of your documents folder so make sure you either manually copy the folder to OneDrive (somewhere else) if you need the data in your cloud (seriously that documents folder contains so many personal data I would not recommand syncing this to a cloud, but that's up to you guys...) or keep it enabled and if you got any problems with ESO addons or SavedVariables disable it then. Reenabling though might get you into trouble with ES again afterwards, so be warned.

    I run ESO on three computers that are synched on OneDrive, I never have any issues. if I make a change on one, eg add a new addon onton PC A and then open PC B as the game is loading in it states it's downloading from OneDrive. I find it incredibly useful, but i will bear it in mind should problems develop.
    Skyrim Red Shirts. Join us at https://skyrimredshirts.co.ukJoin Skyrim Red Shirts. Free trader. We welcome all, from new players to Vets. A mature drama-free social group enjoying PVE questing, PvP, Dungeons, trials and arenas. Web, FB Group & Discord. Guild Hall, trial dummy, crafting, transmutation, banker & merchant. You may invite your friends. No requirements
  • Mathius_Mordred
    Mathius_Mordred
    ✭✭✭✭✭
    N00BxV1 wrote: »
    I'm gonna show how to make an AddOn that can get a list of your guilds and members, and then how to strip all of the Lua from the SaveVariable's text using Regular Expression in Notepad++...

    Create a new folder (Right-click, New Folder) in the AddOns folder called "MyGuilds" (Elder Scrolls Online\live\AddOns\MyGuilds).

    Inside the "MyGuilds" folder create a new file (Right-click, New Text Document) named "MyGuilds.txt" (make sure file extensions are enabled in Windows).

    Open "MyGuilds.txt" with Notepad, copy/paste the text below into it, and then save the file:
    ## Title: MyGuilds
    ## Description: This is my guilds addon.
    ## Author: N00BxV1
    ## Version: 1.0.0
    ## APIVersion: 100044
    ## SavedVariables: MyGuilds_Vars
    MyGuilds.lua
    

    Inside the "MyGuilds" folder create another new file (Right-Click, New Text Document) named "MyGuilds.lua" (make sure file extensions are enabled in Windows).

    Open "MyGuilds.lua" with Notepad, copy/paste the text below into it, and then save the file:
    EVENT_MANAGER:RegisterForEvent("MyGuilds", EVENT_ADD_ON_LOADED, function(eventCode, addonName)
        if addonName ~= "MyGuilds" then return end
        EVENT_MANAGER:UnregisterForEvent("MyGuilds", EVENT_ADD_ON_LOADED)
        -- MyGuilds_Vars is defined in MyGuilds.txt
        MyGuilds_Vars = nil
        MyGuilds_Vars = {}
        for guildIndex = 1, GetNumGuilds() do
            local guildId = GetGuildId(guildIndex)
            local guildName = GetGuildName(guildId)
            MyGuilds_Vars[guildIndex] = {}
            MyGuilds_Vars[guildIndex][guildName] = {}
            for memberIndex = 1, GetNumGuildMembers(guildId) do
                local memberName, _, _, _, _ = GetGuildMemberInfo(guildId, memberIndex)
                MyGuilds_Vars[guildIndex][guildName][memberIndex] = memberName
            end
        end
        for guildIndex, guildTable in pairs(MyGuilds_Vars) do
            for guildName, memberTable in pairs(guildTable) do
                table.sort(memberTable)
            end
        end
    end)
    

    You should now have a folder named "MyGuilds" inside the AddOns folder. And inside of the "MyGuilds" folder there should be two files, one named "MyGuilds.txt" and the other named "MyGuilds.lua".

    Now start the game and login to a character... When your character has fully loaded into the game then type this into chat and press enter:
    /reloadui
    

    The SavedVariables should now be saved to disk. Now quit the game so that we can do the next part...

    Go download Notepad++ if you don't already have it. It's better than Windows Notepad and we need it because it can use Regular Expression.

    Now go to the SavedVariables folder and open the file named "MyGuilds.lua" in Notepad++ (Elder Scrolls Online\live\SavedVariables\MyGuilds.lua).

    The text should look something like this:
    MyGuilds_Vars =
    {
        [1] = 
        {
            ["First Guild Name"] = 
            {
                [1] = "@UserNameA",
                [2] = "@UserNameB",
                [3] = "@UserNameC",
                [4] = "@UserNameD",
                [5] = "@UserNameE",
            },
        },
        [2] = 
        {
            ["Second Guild Name"] = 
            {
                [1] = "@UserNameF",
                [2] = "@UserNameG",
                [3] = "@UserNameH",
                [4] = "@UserNameI",
                [5] = "@UserNameJ",
            },
        },
        [3] = 
        {
            ["Third Guild Name"] = 
            {
                [1] = "@UserNameK",
                [2] = "@UserNameL",
                [3] = "@UserNameM",
                [4] = "@UserNameN",
                [5] = "@UserNameO",
            },
        },
        [4] = 
        {
            ["Fourth Guild Name"] = 
            {
                [1] = "@UserNameP",
                [2] = "@UserNameQ",
                [3] = "@UserNameR",
                [4] = "@UserNameS",
                [5] = "@UserNameT",
            },
        },
        [5] = 
        {
            ["Fifth Guild Name"] = 
            {
                [1] = "@UserNameU",
                [2] = "@UserNameV",
                [3] = "@UserNameW",
                [4] = "@UserNameX",
                [5] = "@UserNameY",
            },
        },
    }
    

    Press Ctrl+A in Notepad++ to Select All text.

    Press Ctrl+H in Notepad++ to open the Find/Replace window.

    In the Replace window under "Search Mode" select "Regular expression". Leave all other options unchecked.

    In the Replace window's "Find what" box enter this text:
    ".+?"(*SKIP)(*F)|.
    

    In the Replace window's "Replace with" box leave it empty.

    Now click "Replace All" and that should remove all of the Lua parts of the text.

    In the Replace window's "Find what" box enter this text:
    \r\n\r\n\r\n
    

    In the Replace window's "Replace with" box leave it empty.

    Now click "Replace All" and that should remove a bunch of empty lines.

    In the Replace window's "Find what" box enter this text:
    "
    

    In the Replace window's "Replace with" box leave it empty.

    Now click "Replace All" and that should remove all of the quotation marks.

    Finally you're done and should have some text that looks like this:
    First Guild Name
    
    @UserNameA
    @UserNameB
    @UserNameC
    @UserNameD
    @UserNameE
    
    Second Guild Name
    
    @UserNameF
    @UserNameG
    @UserNameH
    @UserNameI
    @UserNameJ
    
    Third Guild Name
    
    @UserNameK
    @UserNameL
    @UserNameM
    @UserNameN
    @UserNameO
    
    Fourth Guild Name
    
    @UserNameP
    @UserNameQ
    @UserNameR
    @UserNameS
    @UserNameT
    
    Fifth Guild Name
    
    @UserNameU
    @UserNameV
    @UserNameW
    @UserNameX
    @UserNameY
    
    

    :D

    P.S: Don't mind the weird coloring of the text in the Code blocks, that's just how it works on these forums...

    Cool, good work but my solution is a lot easier although it does require ATT, although I bet most guild masters have that running anyway. Why don't you create and actual addon and submit it to ESOUI, your addon would do all the tedius stripping of LUA crap and give us a simple export from the roster window. I would do it but I don't know LUA, I spent years learning Javascript and Google Sheets and at my age I don't want to learn anything else lol.
    Skyrim Red Shirts. Join us at https://skyrimredshirts.co.ukJoin Skyrim Red Shirts. Free trader. We welcome all, from new players to Vets. A mature drama-free social group enjoying PVE questing, PvP, Dungeons, trials and arenas. Web, FB Group & Discord. Guild Hall, trial dummy, crafting, transmutation, banker & merchant. You may invite your friends. No requirements
  • Mathius_Mordred
    Mathius_Mordred
    ✭✭✭✭✭
    Quoted post has been removed

    Perhaps you didn't bother reading the first post where I have released a solution to this problem which I worked long and hard at, it is very successful and I use it every day. Both my solution and the above is possibly too complex for the average user hence my idea would be an actual addon, which there used to be but which now no longer works. There is no need for you to get your keyboard warrior boxing gloves on, this isn't the place for animosity or nasty remarks.

    @ZOS_Kevin I see no purpose in keeping this open if it's going to generate snide remarks. Please close.
    Edited by ZOS_GregoryV on 4 October 2024 01:17
    Skyrim Red Shirts. Join us at https://skyrimredshirts.co.ukJoin Skyrim Red Shirts. Free trader. We welcome all, from new players to Vets. A mature drama-free social group enjoying PVE questing, PvP, Dungeons, trials and arenas. Web, FB Group & Discord. Guild Hall, trial dummy, crafting, transmutation, banker & merchant. You may invite your friends. No requirements
  • ZOS_GregoryV
    Greetings,

    Per the OP's request, this thread has been closed.

    Regards,
    -Greg-
    The Elder Scrolls Online: Tamriel Unlimited - ZeniMax Online Studios
    Forum Rules | Code of Conduct | Terms of Service | Home Page | Help Site
    Staff Post
This discussion has been closed.