Guild Roster Dumper to Google Sheets

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)



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

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

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

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.

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.

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

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.

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.

ZOS_GregoryV

Greetings,

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

Regards,
-Greg-