# Help for bot twitch writing in excel sheet

**URL:** <https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805>\
**Category:** Share Your Projects\
**Tags:** function-node\
**Created:** [22 January 2024 18:04 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805 "2024-01-22T18:04:18Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [22 January 2024 18:04 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/1 "2024-01-22T18:04:18Z")

</div>

Hello everyone. I'm working on my twitch bot.  
My goal is to be able to create racing tournaments on my channel.  
For that i create a bot twich to help me record participants and their results from commands in chat.  
In this case i'm using node-red-contrib-excelsheets to register results on local files.

First i get all twitch display-name (pseudo) and register them in my excel sheet. (this part is workin well)

Then with a command like this one : !€b2 (with b referring to the race performed and which will give us the correct column to write in. And 2 being the place where the spectator finished his race.)  
My bot is supposed to check the excel file to find the pseudo of my viewer who write the command. Then in the same ligne write in the right collumn his place. And currently this is not working. So maybe i push it too far in my function. Can someone take a look pls ?

There is no Node-red erreur while i execute this function. It is just not writing in my excel or corrupt the excel file.

My second issue is the switch node. i want to use this expression to let message in this type going throw but right now it is not working... : `/(!€[a-z][1-8])/`

![Capture d'écran 2024-01-22 190335](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/b/1b53cd2004d9fce7d75682a3304d08558d264b10.png)

Thanks in advance.

So this is the code of the function :

```let
let message = msg.payload.message;

// Vérification si le pseudo est vide
if (pseudo.trim() === "") {
    // Continuer la vérification sans effectuer d'action spécifique
} else {
    function assignPlace(message) {
        let place = -1; // Valeur par défaut si aucun chiffre n'est trouvé dans le message
        // Recherche des chiffres de 1 à 8 dans le message
        for (let i = 1; i <= 8; i++) {
            if (message.includes(i.toString())) {
                place = i; // Si le chiffre est trouvé, la valeur est définie sur ce chiffre
                break; // Sortir de la boucle une fois le chiffre trouvé
            }
        }

        return place;
    }
    
    // Utilisation de la fonction pour assigner une place en fonction du message
    let place = assignPlace(message);

    // Nouvelle fonction pour assigner une colonne en fonction de la lettre
    function assignColonne(lettre) {
        let colonne = lettre.charCodeAt(0) - 95; // Convertit la lettre en code ASCII et soustrait 95 pour obtenir le numéro de colonne
        return colonne;
    }

    // Utilisation de la fonction pour assigner une colonne en fonction de la lettre
    let colonne = assignColonne(message.toLowerCase()); // Convertit la lettre en minuscule avant de l'assigner à la colonne

    // Vérifier si msg.payload est un tableau
    if (!Array.isArray(msg.payload)) {
        // Si ce n'est pas le cas, le convertir en un tableau contenant l'élément actuel de msg.payload
        msg.payload = [
            {
                header: {
                    col1: "Pseudos"
                    // ... ajoutez des en-têtes de colonnes vides jusqu'à la colonne correspondant à la lettre
                    // colX: "Lettre" par exemple
                },
                items: [],
                sheetName: "InscriptionPlay"
            }
        ];
    }

    // Recherche du pseudo dans la feuille de calcul et ajout de la place sur la même ligne
    let foundPseudo = false;
    for (let i = 0; i < msg.payload.length; i++) {
        if (msg.payload[i].items[0] && msg.payload[i].items[0].col1 === pseudo) {
            msg.payload[i].items[0]["col" + colonne] = place; // Assigne la place à la colonne correspondant à la lettre
            foundPseudo = true;
            break; // Sortir de la boucle après avoir trouvé le pseudo
        }
    }

    // Si le pseudo n'a pas été trouvé, l'ajouter à la feuille de calcul
    if (!foundPseudo) {
        msg.payload.push({
            header: {
                col1: "Pseudos"
                // ... ajoutez des en-têtes de colonnes vides jusqu'à la colonne correspondant à la lettre
                // colX: "Lettre" par exemple
            },
            items: [
                {
                    col1: pseudo,
                    // ... ajoutez des colonnes vides jusqu'à la colonne correspondant à la lettre "x"
                    ["col" + colonne]: place
                }
            ],
            sheetName: "InscriptionPlay"
        });
    }

    msg.filepath = "thecorrectfilepath.xlsx";

    return msg;
}
type or paste code here

```

this is an overview of my flow.

 ![Capture d'écran 2024-01-22 184430](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/b/4b7c707484d5d2c14c1c275c3bc329e2139cccae.png)

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [22 January 2024 18:09 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/2 "2024-01-22T18:09:22Z")

</div>

Sorry i share the wrong code. You will find the right one here :

```auto
let pseudo = msg.payload.userstate["display-name"];
let message = msg.payload.message;

// Vérification si le pseudo est vide
if (pseudo.trim() === "") {
    // Continuer la vérification sans effectuer d'action spécifique
} else {
    function assignPlace(message) {
        let place = -1; // Valeur par défaut si aucun chiffre n'est trouvé dans le message
        // Recherche des chiffres de 1 à 8 dans le message
        for (let i = 1; i <= 8; i++) {
            if (message.includes(i.toString())) {
                place = i; // Si le chiffre est trouvé, la valeur est définie sur ce chiffre
                break; // Sortir de la boucle une fois le chiffre trouvé
            }
        }

        return place;
    }
    
    // Utilisation de la fonction pour assigner une place en fonction du message
    let place = assignPlace(message);

    // Nouvelle fonction pour assigner une colonne en fonction de la lettre
    function assignColonne(lettre) {
        let colonne = lettre.charCodeAt(0) - 95; // Convertit la lettre en code ASCII et soustrait 95 pour obtenir le numéro de colonne
        return colonne;
    }

    // Utilisation de la fonction pour assigner une colonne en fonction de la lettre
    let colonne = assignColonne(message.toLowerCase()); // Convertit la lettre en minuscule avant de l'assigner à la colonne

    // Vérifier si msg.payload est un tableau
    if (!Array.isArray(msg.payload)) {
        // Si ce n'est pas le cas, le convertir en un tableau contenant l'élément actuel de msg.payload
        msg.payload = [
            {
                header: {
                    col1: "Pseudos"
                    // ... ajoutez des en-têtes de colonnes vides jusqu'à la colonne correspondant à la lettre
                    // colX: "Lettre" par exemple
                },
                items: [],
                sheetName: "InscriptionPlay"
            }
        ];
    }

    // Recherche du pseudo dans la feuille de calcul et ajout de la place sur la même ligne
    let foundPseudo = false;
    for (let i = 0; i < msg.payload.length; i++) {
        if (msg.payload[i].items[0] && msg.payload[i].items[0].col1 === pseudo) {
            msg.payload[i].items[0]["col" + colonne] = place; // Assigne la place à la colonne correspondant à la lettre
            foundPseudo = true;
            break; // Sortir de la boucle après avoir trouvé le pseudo
        }
    }

    // Si le pseudo n'a pas été trouvé, l'ajouter à la feuille de calcul
    if (!foundPseudo) {
        msg.payload.push({
            header: {
                col1: "Pseudos"
                // ... ajoutez des en-têtes de colonnes vides jusqu'à la colonne correspondant à la lettre
                // colX: "Lettre" par exemple
            },
            items: [
                {
                    col1: pseudo,
                    // ... ajoutez des colonnes vides jusqu'à la colonne correspondant à la lettre "x"
                    ["col" + colonne]: place
                }
            ],
            sheetName: "InscriptionPlay"
        });
    }

    msg.filepath = "thecorrectfilepath.xlsx";

    return msg;
}

```

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [22 January 2024 18:31 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/3 "2024-01-22T18:31:56Z")

</div>

Hi @Kabrut - Welcome to the forums

Sorry, but i'll start with saying I haven't gone down your code in detail - so my apologies if I am missing something critical.

But what I am seeing is excel, files, columns, lower case, array index's and a lot of 'noisy' code.  
Have you not considered using an SQLite database?

Granted, you will need to understand SQL if not already , but if you do, any reason you haven't?

Using excel files seem chaotic?

Again, My apology if there is a reason for this approach.

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [22 January 2024 19:18 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/4 "2024-01-22T19:18:33Z")

</div>

Hello thank you very much!

My choices are due to the fact that I am a beginner in programming and I know nothing about databases.  
So I suspect that there is probably some excess in my code. This is one reason why I need an outside opinion.

So I tried to make it "simple" by sending the information to an Excel table.  
I chose to send the information to Excel because it is a tool that I know. And to "simply" add values ​​I think it's the simplest for me, if I then want to format my tournament results.

It is ok to ask. 😉

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [22 January 2024 20:03 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/5 "2024-01-22T20:03:15Z")

</div>

Cool!

And I hope you take it as constructive criticism 😃

This is what my approach will be, and then you can build on it.  
I should note: I know nothing about twitch - so you will have a much easier time then I (and likely a few of us) 😄

So...

Note : This is just an idea on how to simply the approach,  
and will help you explore JS at the same time. 🤓

You can do a lot of this without using function nodes, but I think the function approach will help you understand Javascript (which is the basis of all the other nodes)

Node RED has a context mechanism, which you can store values/tables etc.

1. Create the storage area and call it **tournament**  
This creates on **object** in global context. Do this **only once** for each tournament

```auto
const myStorageObject = {
      "players": [],
      "races": []
}
global.set("tournament", myStorageObject)

```

1. Whenever your bot receives a player result

```auto
/* Load our global state */
const tournament = global.get("tournament");

/* Player Name, Race Name, Place */
const playerName = msg.payload.userstate["display-name"]
const someRaceName = msg.payload.userstate["race-name"]
const place = msg.payload.userstate["place"]

/* Add Player */
if (!tournament.players.find((P) => P === playerName)) {
    tournament.players.push(playerName)
}

/* Add Race */
if (!tournament.races.find((R) => R.name === someRaceName)) {
    tournament.races.push({
        "name": someRaceName,
        "players": []
    })
}

/* Get Race */
const race = tournament.races.find((R) => R.name === someRaceName))

/* Only allow players to add their result for each race once! */
if (!race.players.find((P) => P.player === playerName)) {
    race.players.push({
        "player": playerName,
        "place": place
    })
}

```

once this has filled with data - you can use it to calculate the winners of the tournament so on and so forth.

you can view this **tournament** object in the context tab in the Editor, and use it for other things - such as creating the final excel sheet after the tournament if you wanted it as a file

I hope this helps you with your project 😄

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [22 January 2024 20:13 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/6 "2024-01-22T20:13:12Z")

</div>

Thank you really much for this approach !

Effectively, I already used a global context to be able to register the participants and to be able to carry out random draws.

But I hadn't thought of using context to add other information.

Thank you for this point of view, I will try to explore this approach. 😁

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [22 January 2024 20:26 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/7 "2024-01-22T20:26:17Z")

</div>

Using **context** will be the database if you will for your bot.

- Command to return the current rankings in the tournament.
- Command for each player to be able to check their last race results (or a chosen race they want to lookup) - and total points

etc etc

You know better what is off interest here 😃 , but using **context** will be much easier.  
But please don't let me interfere with it - its just the way I will go myself

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [22 January 2024 20:45 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/8 "2024-01-22T20:45:13Z")

</div>

For a first bot i think your approach is easier.  
For now i want it to work.

But i will think about how to go further too. And what action i want to add 😉

I really thank you because I knew that, for a first attempt, I had a complex approach and that there was a simpler one.

But when you start down a path it's always difficult to step back. Especially when you're a beginner. 🤗

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [25 January 2024 19:59 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/9 "2024-01-25T19:59:25Z")

</div>

Hello everyone. Please check this update.  
I've been deeper on Marcus'point of view so i am now using the context to do my things. It is simpler but i just have 1 little problem.

I have now this context set up :

```auto
const myStorageObject = {
    "players": [],
    "races": [],
    "resultats": []
}
global.set("tournament", myStorageObject)
return msg;

```

```auto
/* Load our global state */
const tournament = global.get("tournament");

/* pseudo */
const pseudo = msg.payload.userstate["display-name"]

/* Add Player */
if (!tournament.players.find((P) => P === pseudo)) {
    tournament.players.push(pseudo)
}

return msg;

```

So now i have all my players register in my tournament

But my command to add all results and races is not working... someRaceName, place and pseudo are not added to the context.  
The message send by the player is like that : "!a2"  
where "a" is the name of the race and "2" the result of the player. (the name of the race can be a letter between a and z and the result can be a number between 1 and 8.

I'm using a regular expression but i don't know if it is good or not or if it's something else that is wrong in my code.

```auto
/* Load our global state */
const tournament = global.get("tournament");

/* Player Name, Race Name, Place */
const pseudo = msg.payload.userstate["display-name"]
const message = msg.payload.message;

const regexRaceName = /^![a-z].*$/;
const regexPlace = /^![a-z][1-8]$/;

/* Vérifier si le message contient une seule lettre pour someRaceName et un chiffre entre 1 et 8 pour place */
if (regexRaceName.test(message[1]) && regexPlace.test(message[2])) {
    const someRaceName = message[1];
    const place = parseInt(message[2]); // Convertir le chiffre en entier

/* Add Player */
if (!tournament.players.find((P) => P === pseudo)) {
    tournament.players.push(pseudo)
}

/* Add Race */
if (!tournament.races.find((R) => R.name === someRaceName)) {
    tournament.races.push({
        "name": someRaceName,
        "players": []
    })
}

/* Get Race */
const race = tournament.races.find((R) => R.name === someRaceName)

/* Only allow players to add their result for each race once! */
if (!race.players.find((P) => P.player === pseudo)) {
    race.players.push({
        "player": pseudo,
        "place": place
    })
}
}
return msg;

```

I will continue my investigations. I will update this topic if i found something.  
If someone have an idea about what's wrong i really thank you in advance.  
have a nice day! 😁

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [25 January 2024 20:14 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/10 "2024-01-25T20:14:43Z")

</div>

Hi @Kabrut

Glad I didn't scare you off 😉

What would help here I think, is a complete example of the `payload` that you receive and is consumed by this script?

My regex is....... none, but plenty here who are gods at it!  
having an example will help a lot - feel free to replace real names of course

**EDIT**

* * *

And just in case, ensure this is only created once (for each tournament)  
else it will be cleared/recreated each time a message is received.

```auto
const myStorageObject = {
    "players": [],
    "races": [],
    "resultats": []
}
global.set("tournament", myStorageObject)
return msg;

```

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [25 January 2024 20:20 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/11 "2024-01-25T20:20:54Z")

</div>

Hi Marcus.  
Thank you to be there.

The payload is set up like this for twitch :  
 ![image_2024-01-25_211853005](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/b/2baf147c578f7c96263eb2b2eb6fd3fd86ffe5cc.png)

I hope it will help

I already try to change the expression for this :

```auto
const regexRaceName = /^"![a-z].*$/;
const regexPlace = /^"![a-z][1-8]"$/;

```

but nothing too

My regex is also close to the void 😅

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [25 January 2024 20:24 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/12 "2024-01-25T20:24:24Z")

</div>

yes I have already experienced the context which resets with each new pseudo and I have already fixed the problem.😁  
It is set up on a separate node with a separate command. and it works. I have several registrants who arrive in my context like intended.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [25 January 2024 20:36 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/13 "2024-01-25T20:36:12Z")

</div>

Ok would this work?

```auto
const validMessageRegex = /^i[a-z][1-8]$/
if (validMessageRegex.test(message)) {
   ....Your Code
}

```

This was in no way suggested by ChatGPT 😉

**EDIT**

* * *

Also to see where it might be stopping...

add `node.warn("Hello, Wold")` in places in code - they will appear in the debug pane

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [25 January 2024 20:44 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/14 "2024-01-25T20:44:49Z")

</div>

yes i found it too. That was like always very "obvious".  
the first regex was good.

Now that's the correct code :

```auto
if (regexRaceName.test(message) && regexPlace.test(message)) {
    const someRaceName = message[1];
    const place = parseInt(message[2]);

```

the problem was that i called the full regex to check if it feet only with the second or 3rd character. But of course the code was searching the full 3 character to fit with just 1 so it doesn't work 🤪

but now it's working.

Once again thank you to be there 😉  
You really help !

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [25 January 2024 20:50 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/15 "2024-01-25T20:50:16Z")

</div>

I don't _think_ you need 2 tests.

```auto
const validMessageRegex = /^i[a-z][1-8]$/
validMessageRegex.test(message)

```

will match "iAN"

where **A** can be **a-z** and N can be **1-8**

a single test should validate the whole format (I think)

but glad your getting there 😃

---

<div class="post-metadata">

**Author:** ![Kabrut](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@Kabrut](https://discourse.nodered.org/u/Kabrut)\
**Post date:** [25 January 2024 20:52 UTC](https://discourse.nodered.org/t/help-for-bot-twitch-writing-in-excel-sheet/84805/16 "2024-01-25T20:52:56Z")

</div>

Yes, Effectively, just one check is needed.  
Thank you it will simplify a bit the code. 😉