Google Sheets API support

[SenorKarlos]

Displaying stats is awesome, working with those stats without manual input would be freakin stellar :D

Per our conversation, I'd like to be able to link most or all the counts being set in config to their own cell (if you want to do name, 2 cells)

I'd guess the logical way would be to set a spreadsheet id per guild and cell ID(s) per channel being calculated. I couldn't figure out how to set the active sheet but probably just sheetname$A1.

Idea of how the new config would be. Possibly set the gsheetKey per server instead near the Discord token? though I doubt more than one key would be needed, it's unlimited usage with a ratelimit of like 300/min

{
    "logLevel": 3,
    "updateIntervalM": 15,
    "gsheetKey": "",
    "servers": {
        "00000000000001": {
            "ownerId": 00000000000001,
            "guildId": 00000000000001,
            "gsheetId": "",
            "memberCountChannel": {
                "id": 00000000000001,
                "nameCell": "",
                "countCell": ""
            },
            "botCountChannel": {
                "id": 00000000000001,
                "nameCell": "",
                "countCell": ""
            },
            "roleCountChannel": {
                "id": 00000000000001,
                "nameCell": "",
                "countCell": ""
            },
            "channelCountChannel": {
                "id": 00000000000001,
                "nameCell": "",
                "countCell": ""
            },
            "memberRoles": {
                "00000000000001": {
                    "roleIds": [00000000000001],
                    "text": "Supporters",
                    "nameCell": "",
                    "countCell": ""
                }
            },
            "token": "<DISCORD_BOT_TOKEN>",
            "status": null,
            "logLevel": 3,
        }
    }
}

[clburlison]

what do you plan on doing with this data? Also uploading to one sheet with the following output would be much easier from a coding/management standpoint.

Guild	name	value
552003258000998401	Bot Count	13
552003258000998401	Role Count	45

you also need a GCP project, IAM service account, correct API to Google Sheets, and the json key to make these requests which is why one googlesheet is easier.

But again before we start problem solving how to do this...I'm super curious what value you're getting from this information?

[SenorKarlos]

tbh the "default counts" aren't really necessary for this (nor would posting the guild ID), just figured it could be done at the same time in case someone else needs it, and functions are resuable right?

My main goal is in the custom role counts. My service activation involves assigning themselves a unique area role in my primary guild and then as many areas as they want alerts for in the other servers I set up by major area. I need those area user counts to determine where I should be reducing or improving device coverage. I've got a large service footprint and was doing this via a gForm and linked response sheet, but maintaining who left was a chore. Now the chore is gathering the data and putting it into the spreadsheet.

If using multiple sheets is a pain from a programming standpoint, I can just use the first sheet (or completely standalone gSheet) as my 'Import Tab' as before and work the data out into other sheets. I didn't expect anyone but versx to be on this though 😂 we've been chatting in DM about GS issues and improvements and he asked me to make this.

[clburlison]

Okay. I don't use this tool but interact with GoogleSheets via the API quite heavily for work. Figured I'd add my two cents for path of least resistance.

My overall recommendation if versx or someone else wants to implement.

• use one Google sheet
• Throw everything into a named sheet. If you use the initial sheet it will be gid=0
• For the config file use "gsheet_auth": "/path/to/gcp_service_id.json"

Good luck with your complex setup. 🙃

[SenorKarlos]

The input is appreciated for sure. I know nothing of it all and I'm guessing versx is learning it to do this. So in your config suggestion there would be a whole other json to set cells and stuff in? I'm probably understanding wrong cuz it sounds more complicated than needed to my layman brain :D I'm just thinking there will be a function call to update cells and those variables should be easy to input/correlate to the data source.

My current sheet which I planned to just update and modify, has an initial sheet named 'Import' which used to pull data from that other sheet I mentioned, and then several sheets for each major area, containing all the serviced zones. That should be fine then, just use that first sheet and it'll be the gid+0 you mentioned?

And thanks, I need it 😅