I can’t manage to fetch the discord global_username from members on my servers when using their <@UserID> in a google spreadsheet script. More below.

Hello, first time posting and I’m in a bit of a pickle. I’m trying to connect through a discord bot my google spreadsheet to a server discord in which I’ve got admin perms.

The objective here is simple and shouldn’t be hard, what I want is that by linking my spreadsheet to Discord’s API with the bot token, I want to input in a cell the <@UserID> and have it update the cell to its right so it shows the global_username linked to said UserID.

I’ve been on it for more time than I’d like to admit and I could use some help.

Here’s the code I’ve had:

const DISCORD_BOT_TOKEN = 'MY TOKEN';

function getDiscordUsername(userID) {
  if (typeof userID !== 'string' || userID.trim() === '') {
    return 'Invalid UserID';
  }

  if (userID.startsWith('<@') && userID.endsWith('>')) {
    userID = userID.slice(2, -1);
  } else if (userID.startsWith('@')) {
    userID = userID.slice(1);
  }

  const url = `https://discord.com/api/v10/users/${userID}`;
  
  const options = {
    'method': 'get',
    'headers': {
      'Authorization': `Bot ${DISCORD_BOT_TOKEN}`
    }
  };
  
  try {
    const response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() === 200) {
      const json = JSON.parse(response.getContentText());
      return json.username + '#' + json.discriminator;
    } else {
      return 'User not found';
    }
  } catch (e) {
    return 'Error fetching user';
  }
}

function onEdit(e) {
  if (!e || !e.range) {
    console.error('No event object or range found.');
    return;
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets();


  sheets.forEach(sheet => {
    const range = e.range;
    const sheetName = sheet.getName();
    

    if (sheetName !== 'Feuille1' && sheetName !== 'Feuille2') {
      const userID = e.value;
      if (userID) {
        const username = getDiscordUsername(userID);
        sheet.getRange(range.getRow(), 2).setValue(username);
      }
    }
  });
}