Google Sheet Dropdown

/* eslint-disable */

enableDebugMode();

/**
 * Questmate Custom Item - Google Sheets Member Picker
 *
 * Permissions:
 * @UseApp {GOOGLE}
 *
 * Changelog:
 * v0.1 Initial release
 */

return defineCustomItem((Questmate) => {
  Questmate.registerView("ITEM_RUN_VIEW", ({ useRunData, useConfigData }) => {
    const [spreadsheetId] = useConfigData("spreadsheetId");
    const [sheetId] = useConfigData("sheetId");
    const [columnName] = useConfigData("columnName");
    const [selectedRowValue, setSelectedRowValue] = useRunData(
      "rowValue",
      null
    );

    if (spreadsheetId === null || sheetId === null || columnName === null) {
      return {
        components: [],
      };
    }

    return {
      components: [
        {
          id: "row",
          type: "dropdown",
          title: "Member",
          icon: "person",
          value: selectedRowValue,
          onSelect: setSelectedRowValue,
          getOptions: async () => {
            const sheets = await sheetsDataSource.retrieve(spreadsheetId);

            const selectedSheet = sheets.find(
              (sheet) => sheet.properties.sheetId === sheetId
            );
            const sheetName = selectedSheet?.properties?.title;
            if (!sheetName) {
              console.log(
                "ERROR: Invalid configuration, invalid sheetName.",
                JSON.stringify({
                  spreadsheetId,
                  sheetId,
                  columnName,
                  sheetName,
                })
              );
              return [];
            }

            const header = await sheetHeaderDataSource.retrieve(
              spreadsheetId,
              sheetName
            );
            const columnIndex = header?.rowValues?.indexOf(columnName);
            if (columnIndex === undefined || columnIndex === -1) {
              console.log(
                "ERROR: Invalid configuration, invalid columnIndex.",
                JSON.stringify({
                  spreadsheetId,
                  sheetId,
                  columnName,
                  sheetName,
                  columnIndex,
                })
              );
              return [];
            }

            const columnNumber = columnIndex + 1;
            const startingRowNumber = header.rowNumber + 1;

            const columnValues = await columnValuesDataSource.retrieve(
              spreadsheetId,
              sheetName,
              columnNumber,
              startingRowNumber
            );

            if (!Array.isArray(columnValues)) {
              console.log(
                "ERROR: Invalid configuration, invalid columnValues.",
                JSON.stringify({
                  spreadsheetId,
                  sheetId,
                  columnName,
                  sheetName,
                  columnIndex,
                  columnNumber,
                  startingRowNumber,
                  columnValues,
                })
              );
              return [];
            }

            return columnValues
              .map((value) => ({
                label: value,
                value,
              }))
              .sort((a, b) =>
                a.label.localeCompare(b.label, undefined, {
                  numeric: true,
                  sensitivity: "base",
                })
              );
          },
          optionNoun: "Member",
          optionPluralNoun: "Members",
        },
      ],
    };
  });

  Questmate.registerView("ITEM_CONFIG_VIEW", async ({ useConfigData }) => {
    const [selectedSpreadsheetId, setSelectedSpreadsheetId] = useConfigData(
      "spreadsheetId",
      null
    );
    const [selectedSheetId, setSelectedSheetId] = useConfigData(
      "sheetId",
      null
    );
    const [selectedColumnName, setSelectedColumnName] = useConfigData(
      "columnName",
      null
    );

    const viewComponents = [];
    viewComponents.push({
      id: "spreadsheet",
      title: "Google Spreadsheet",
      type: "dropdown",
      value: selectedSpreadsheetId,
      getOptions: async () => {
        return (await spreadsheetsDataSource.retrieve()).map((spreadsheet) => ({
          label: spreadsheet.name,
          value: spreadsheet.id,
        }));
      },
      onSelect: (value) => {
        if (value !== selectedSpreadsheetId) {
          setSelectedSpreadsheetId(value);
          setSelectedSheetId(null);
          setSelectedColumnName(null);
        }
      },
      optionNoun: "Spreadsheet",
      optionPluralNoun: "Spreadsheets",
    });

    if (selectedSpreadsheetId !== null) {
      viewComponents.push({
        id: "sheet",
        title: "Sheet",
        type: "dropdown",
        value: selectedSheetId,
        getOptions: async () => {
          return (await sheetsDataSource.retrieve(selectedSpreadsheetId)).map(
            (sheet) => ({
              label: sheet.properties.title,
              value: sheet.properties.sheetId,
            })
          );
        },
        onSelect: (value) => {
          if (value !== selectedSheetId) {
            setSelectedSheetId(value);
            setSelectedColumnName(null);
          }
        },
        optionNoun: "Sheet",
        optionPluralNoun: "Sheets",
      });
    }

    if (selectedSheetId !== null) {
      viewComponents.push({
        id: "column",
        title: "Column",
        type: "dropdown",
        value: selectedColumnName,
        getOptions: async () => {
          const sheets = await sheetsDataSource.retrieve(selectedSpreadsheetId);
          const selectedSheet = sheets.find(
            (sheet) => sheet.properties.sheetId === selectedSheetId
          );

          const sheetName = selectedSheet?.properties?.title;
          if (sheetName === undefined) {
            throw new Error("Selected Spreadsheet not found!");
          }

          const header = await sheetHeaderDataSource.retrieve(
            selectedSpreadsheetId,
            sheetName
          );
          return header.rowValues
            .filter(Boolean)
            .filter((value, index, self) => self.indexOf(value) === index)
            .map((value) => ({
              label: value,
              value: value,
            }));
        },
        onSelect: setSelectedColumnName,
        optionNoun: "Column",
        optionPluralNoun: "Columns",
      });
    }

    return {
      components: viewComponents,
    };
  });

  const spreadsheetsDataSource = Questmate.registerDataSource({
    id: "spreadsheets",
    initialData: [],
    refreshInterval: 120,
    fetcher: () => async () => {
      const spreadsheetsData = await fetch(
        "https://www.googleapis.com/drive/v3/files?q=mimeType='application/vnd.google-apps.spreadsheet'"
      );
      const { files: spreadsheets } = await spreadsheetsData.json();
      return {
        data: spreadsheets,
      };
    },
  });

  const sheetsDataSource = Questmate.registerDataSource({
    id: "sheets",
    initialData: [],
    refreshInterval: 120,
    fetcher: () => async (spreadsheetId) => {
      const spreadsheetData = await fetch(
        `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}`
      );
      const { sheets } = await spreadsheetData.json();
      return {
        data: sheets,
      };
    },
  });

  const sheetHeaderDataSource = Questmate.registerDataSource({
    id: "sheetHeader",
    initialData: {
      rowValues: [],
      rowNumber: -1,
    },
    refreshInterval: 30,
    fetcher: () => async (spreadsheetId, sheetTitle) => {
      const sheetData = await fetch(
        `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodeURIComponent(
          sheetTitle
        )}!1:5`
      );
      const { values: rows } = await sheetData.json();
      const headerRowIndex = rows.findIndex((row) => row.length > 0);

      return {
        data: {
          rowValues: rows[headerRowIndex],
          rowNumber: headerRowIndex + 1,
        },
      };
    },
  });

  const columnValuesDataSource = Questmate.registerDataSource({
    id: "columnValues",
    initialData: [],
    refreshInterval: 60,
    fetcher:
      () =>
      async (spreadsheetId, sheetName, columnNumber, startingRowNumber) => {
        const sheetData = await fetch(
          `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodeURIComponent(
            sheetName
          )}!R${startingRowNumber}C${columnNumber}:C${columnNumber}`
        );
        const { values } = await sheetData.json();

        return {
          data: values
            .map((rowPart) => rowPart[0])
            .filter((value) => typeof value === "number" || Boolean(value))
            .filter((value, index, self) => self.indexOf(value) === index),
        };
      },
  });
});