Export data to Google Sheets

Custom Component Example (Including configuration UI)

/* eslint-disable */

enableDebugMode();

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

return defineCustomItem((Questmate) => {
  Questmate.registerItemRunHandler(async ({ useConfigData, useQuest }) => {
    const [spreadsheetId] = useConfigData("spreadsheetId");
    const [sheetId] = useConfigData("sheetId");
    const [columnToItemMap] = useConfigData("columnToItemMap");

    const sheets = await sheetsDataSource.retrieve(spreadsheetId);

    const selectedSheet = sheets.find(
      (sheet) => sheet.properties.sheetId === sheetId
    );
    const sheetTitle = selectedSheet?.properties?.title;
    if (!sheetTitle) {
      console.log(
        "ERROR: Invalid configuration, invalid sheetTitle.",
        JSON.stringify({
          spreadsheetId,
          sheetId,
          sheetTitle,
          columnToItemMap,
        })
      );
      throw new Error("ERROR: Invalid configuration, invalid sheetTitle.");
    }

    const header = await sheetHeaderDataSource.retrieve(
      spreadsheetId,
      sheetTitle
    );

    const quest = await useQuest();
    const questRun = await quest.getRun();

    const newRowValues = await Promise.all(
      header.rowValues.map(async (headerName) => {
        const item = questRun.getItem(columnToItemMap[headerName]);

        if (item) {
          return item.stringValue;
        }

        return "";
      })
    );

    const insertRowRequest = await fetch(
      `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodeURIComponent(
        sheetTitle
      )}!A:Z:append?valueInputOption=USER_ENTERED`,
      {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
        },
        body: JSON.stringify({
          values: [newRowValues],
        }),
      }
    );

    const insertRowResponseData = await insertRowRequest.json();
  });

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

      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);
          }
        },
        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);
            }
          },
          optionNoun: "Sheet",
          optionPluralNoun: "Sheets",
        });
      }

      if (selectedSheetId !== null) {
        const sheets = await sheetsDataSource.retrieve(selectedSpreadsheetId);

        const selectedSheet = sheets.find(
          (sheet) => sheet.properties.sheetId === selectedSheetId
        );
        const sheetTitle = selectedSheet?.properties?.title;
        if (!sheetTitle) {
          console.log(
            "ERROR: Invalid configuration, invalid sheetTitle.",
            JSON.stringify({
              selectedSpreadsheetId,
              selectedSheetId,
              sheetTitle,
              columnToItemMap,
            })
          );
          return {
            components: [
              {
                id: `NoSheetTitle`,
                type: "text",
                content:
                  sheets.length === 0
                    ? "Loading Sheets..."
                    : "Invalid sheet title.",
              },
            ],
          };
        }
        const header = await sheetHeaderDataSource.retrieve(
          selectedSpreadsheetId,
          sheetTitle
        );
        viewComponents.push({
          id: `TextBlock1`,
          type: "text",
          content: "Choose the item to map to each column below.",
        });
        header.rowValues
          .filter(Boolean)
          .filter((value, index, self) => self.indexOf(value) === index)
          .forEach((columnHeader, index) => {
            viewComponents.push({
              id: `${index}-${columnHeader}`,
              title: columnHeader,
              type: "ItemPicker",
              value: columnToItemMap[columnHeader],
              onSelect: (itemId) => {
                if (itemId !== columnToItemMap[columnHeader]) {
                  setColumnToItemMap({
                    ...columnToItemMap,
                    [columnHeader]: itemId,
                  });
                }
              },
            });
          });
      }

      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?includeItemsFromAllDrives=true&supportsAllDrives=true&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,
        },
      };
    },
  });
});