Preload Data in a Sheet | Flatfile Help Center

Sometimes you may need to preload some data into a sheet or workbook that your users should use for reference or for creating relationships with the data they're importing. This data should appear before they have imported their first file.

To do this, you'll have a few steps you'll want to handle in your listener.

1) Listen for the creation of your workbook, including the sheet that you need to load data into.

2) Grab the ID of the sheet that should receive this data.

3) Upload your records to that sheet.

A very simplified example of this (including the format you'll want for your records) can be seen in the below example:

listener.filter({ job: "space:configure" })
      .on("job:ready", async (event: FlatfileEvent) => {
        const { spaceId, environmentId, jobId } = event.context;
        try {
          await api.jobs.ack(jobId, {
            info: "Gettin started.",
            progress: 10,
          });

          const workbook = await api.workbooks.create({
            spaceId,
            environmentId,
            name: "All Data",
            sheets: [
              {
                name: "Sheet 0",
                slug: "sheet0",

                fields: [
                  {
                    key: "room",
                    type: "string",
                    label: "Room Rented",
                  },
                  {
                    key: "reservationNum",
                    type: "string",
                    label: "Reservation Number",
                  },
                  {
                    key: "email",
                    type: "reference",
                    label: "Email",
                    config: {
                      ref: "sheet2",
                      key: "email",
                      relationship: "has-one",
                    },
                  },
                ],
                actions: [],
                allowAdditionalFields: true,
              },
              {
                name: "Sheet 1",
                slug: "sheet1",
                fields: [
                  {
                    key: "firstName",
                    type: "string",
                    label: "First Name",
                  },
                  {
                    key: "lastName",
                    type: "string",
                    label: "Last Name",
                  },
                  {
                    key: "email",
                    type: "string",
                    label: "Email",
                  },
                ],
                actions: [],
                allowAdditionalFields: true,
              },
            ],
            actions: [
              {
                operation: "submitAction",
                mode: "foreground",
                label: "Submit foreground",
                confirm: true, 
              },
            ],
          });
          await api.jobs.complete(jobId, {
            outcome: {
              message: "Your Space was created. Let's get started.",
              acknowledge: true,
            },
          });
        } catch (error) {
          console.error("Error:", error.stack);

          await api.jobs.fail(jobId, {
            outcome: {
              message: "Creating a Space encountered an error.",
              acknowledge: true,
            },
          });
        }
      });

      listener.on("workbook:created", async (event: FlatfileEvent) => {
        const records = [
          {
            "firstName": {
              "value": "John", 
              "messages": [], 
              "valid": true
            }, 
            "lastName": {
              "value": "Test", 
              "messages": [], 
              "valid": true
            }, 
            "email": {
              "value": "test@test.com",
              "messages": [], 
              "valid": true
            }
          }
        ]

        const {workbookId} = event.context;

        try {
          const workbook = await api.workbooks.get(workbookId); 
          const sheetId = workbook.data.sheets[1].id

          //insert records
          await api.records.insert(sheetId, records)
        } catch (error) {
          console.log("Record Insertion failed:", JSON.stringify(error))
        }
      })

Relational Fields

This workflow is useful when you have relationships you need to make with the data your users are uploading, as it ensures all users will have access to the same data set of relational data regardless of what they are importing.

Since in sheet 0 in the example above, there is a field named "email" configured as a relational field linked to sheet 1's email field, any records that are uploaded to sheet 0 with an email of "test@test.com" would automatically be linked to the pre-loaded record with that same email address in sheet 1. This ensures that relationships can be created quickly and with minimal effort from the end user.