Google Spreadsheet + Discord Bot

discord-gspreadsheet-integration.js

const { Integration } = require('@fusebit-int/framework');
const integration = new Integration();

// Koa Router: https://koajs.com/
const router = integration.router;
const googleConnectorName = '{{GOOGLE CONNECTOR NAME HERE}}';
const discordConnectorName = '{{DISCORD CONNECTOR NAME HERE}}';
const spreadsheetId = '{{ID OF THE SPREADSHEET}}'
const discordChannelId = '{{DISCORD CHANNEL ID TO CREATE THE THREADS}}'

// Test Endpoint: This is an authorized HTTP endpoint you can call from any third party
router.post('/api/tenant/:tenantId/test', integration.middleware.authorizeUser('install:get'), async (ctx) => {
  // API Reference: https://developer.fusebit.io/reference/fusebit-int-framework-integration
  const googleClient = await integration.tenant.getSdkByTenant(ctx, googleConnectorName, ctx.params.tenantId);


  // API Reference: https://github.com/googleapis/google-api-nodejs-client
  // Get spreadsheet metadata so we will know how many groups are created.
  const metadata = await googleClient.sheets('v4').spreadsheets.get({
    spreadsheetId
  });

  const { rowGroups: groups } = metadata.data.sheets[0];
  // Identify how many rows we want to fetch from the spreadsheet
  const { range: { endIndex } } = groups[groups.length - 1];

  // Fetch the desired spreadsheet rows
  const { data: { values } } = await googleClient.sheets('v4').spreadsheets.values.get({
    spreadsheetId,
    range: `A1:${endIndex}`
  });

  const groupList = [];

  // Map rows with groups
  groups.forEach(({ range: { startIndex, endIndex } }) => {
    const groupData = values.slice(startIndex - 1, endIndex).flat();
    groupList.push({
      title: groupData[0], // First item will be always the group name
      items: groupData.slice(1, endIndex),
    });

  });

  // Create a thread for each group

  // For the Discord API documentation, see https://discord.com/developers/docs/reference.
  const discordClient = await integration.tenant.getSdkByTenant(
    ctx,
    discordConnectorName,
    ctx.params.tenantId
  );


  for await (const group of groupList) {
    const { id }  = await discordClient.bot.post(`channels/${discordChannelId}/threads`, { name: group.title, type: 11 });
    await discordSendMessageToChannelId(ctx, id, group.items.join('\n'));
  }
  // https://discord.com/developers/docs/resources/channel#start-thread-without-message

});

module.exports = integration;



// Discord - Send a message to channel Id
// Send a message to a Discord channel identified with an Id.

/**
 * Send a message to a Discord channel identified with an Id. This function requires a custom Discord app with send message 
 * bot permission, see https://developer.fusebit.io/docs/discord#creating-your-own-discord-app.
 * 
 * @param ctx {FusebitContext} Fusebit Context of the request
 * @param channelId {string} Channel Id
 * @param content {string} Message content
 * @returns {object} Discord response
 */
async function discordSendMessageToChannelId(ctx, channelId, content) {
  // For the Discord API documentation, see https://discord.com/developers/docs/reference.
  const discordClient = await integration.tenant.getSdkByTenant(
    ctx,
    discordConnectorName,
    ctx.params.tenantId
  );
  return await discordClient.bot.post(`channels/${channelId}/messages`, { content });
}

This is a basic example of using Google Spreadsheets API to read the grouped rows and fetch the row range values.