Defining Mappings in a Spreadsheet

Oct 13, 2025 by pynkocean.com 74 views

Overview

In this article, we will explore the possibility of setting or mapping a collection from a spreadsheet using the CLI. This feature would enable users to define a bunch of mappings in a spreadsheet (XLS or gsheets) and then pull those mappings into the CLI for further processing. The ultimate goal is to upload these mappings into a collection, making it easier to manage and utilize the data.

Defining Mappings in a Spreadsheet

The first step in this process is to define the mappings in a spreadsheet. This can be done using either XLS or gsheets. The spreadsheet should have two columns, with the first column representing the key and the second column representing the value. If the spreadsheet has more than two columns, we can take a range or two columns to create the key-value pairs.

Ignoring Headers

One of the challenges we may face is determining whether the first two rows in the spreadsheet are headers and can be ignored. While it's probably harmless to include a header in the map, it's still a redundant key. To address this issue, we can add an extra CLI argument to ignore the headers.

CLI Command

The CLI command for setting or mapping a collection from a spreadsheet would be similar to the following:

openfn collections set my-collection my-key --items pathOrUrlToSheet

However, we may need to add extra CLI arguments to specify how to parse the items as a spreadsheet, choose the range, or ignore the headers.

Extra CLI Args

Some possible extra CLI args that we can consider are:

--parse-items-as-spreadsheet: This arg would indicate that the items should be parsed as a spreadsheet.
--choose-range: This arg would allow the user to choose the range of cells to use for the key-value pairs.
--ignore-headers: This arg would ignore the first two rows in the spreadsheet as headers.

Example Use Case

Let's say we have a spreadsheet with the following data:

Key	Value
John	25
Jane	30
Bob	35

We can use the CLI command to set or map a collection from this spreadsheet as follows:

openfn collections set my-collection my-key --items pathOrUrlToSheet --parse-items-as-spreadsheet --choose-range=A1:B3

This command would create a collection with the key-value pairs from the spreadsheet, ignoring the headers.

Benefits

The ability to set or map a collection from a spreadsheet using the CLI would have several benefits, including:

Improved data management: By allowing users to define mappings in a spreadsheet, we can improve data management and reduce errors.
Increased productivity: The CLI command would automate the process of setting or mapping a collection, saving users time and increasing productivity.
Flexibility: The ability to choose the range of cells and ignore headers would provide users with more flexibility when working with spreadsheets.

Conclusion

Q: What is the purpose of setting or mapping a collection from a spreadsheet?

A: The purpose of setting or mapping a collection from a spreadsheet is to enable users to define a bunch of mappings in a spreadsheet (XLS or gsheets) and then pull those mappings into the CLI for further processing. The ultimate goal is to upload these mappings into a collection, making it easier to manage and utilize the data.

Q: How do I define mappings in a spreadsheet?

A: To define mappings in a spreadsheet, you can use either XLS or gsheets. The spreadsheet should have two columns, with the first column representing the key and the second column representing the value. If the spreadsheet has more than two columns, you can take a range or two columns to create the key-value pairs.

Q: What if my spreadsheet has more than two columns?

A: If your spreadsheet has more than two columns, you can take a range or two columns to create the key-value pairs. You can also use the --choose-range CLI arg to specify the range of cells to use for the key-value pairs.

Q: How do I ignore headers in my spreadsheet?

A: To ignore headers in your spreadsheet, you can use the --ignore-headers CLI arg. This will ignore the first two rows in the spreadsheet as headers.

Q: What is the CLI command for setting or mapping a collection from a spreadsheet?

A: The CLI command for setting or mapping a collection from a spreadsheet is:

openfn collections set my-collection my-key --items pathOrUrlToSheet

However, you may need to add extra CLI args to specify how to parse the items as a spreadsheet, choose the range, or ignore the headers.

Q: What are some possible extra CLI args that I can use?

A: Some possible extra CLI args that you can use are:

--parse-items-as-spreadsheet: This arg would indicate that the items should be parsed as a spreadsheet.
--choose-range: This arg would allow you to choose the range of cells to use for the key-value pairs.
--ignore-headers: This arg would ignore the first two rows in the spreadsheet as headers.

Q: How do I use the CLI command to set or map a collection from a spreadsheet?

A: To use the CLI command to set or map a collection from a spreadsheet, you can follow these steps:

Define your mappings in a spreadsheet (XLS or gsheets).
Use the CLI command to set or map a collection from the spreadsheet:

openfn collections set my-collection my-key --items pathOrUrlToSheet --parse-items-as-spreadsheet --choose-range=A1:B3

Replace my-collection and my-key with your actual collection name and key.
Replace pathOrUrlToSheet with the path or URL to your spreadsheet.
Use the --parse-items-as-spreadsheet and --choose-range CLI args to specify how to parse the items as a spreadsheet and choose the range of cells to use for the key-value pairs.

Q: What are the benefits of setting or mapping a collection from a spreadsheet?

A: The benefits of setting or mapping a collection from a spreadsheet include:

Improved data management: By allowing users to define mappings in a spreadsheet, we can improve data management and reduce errors.
Increased productivity: The CLI command would automate the process of setting or mapping a collection, saving users time and increasing productivity.
Flexibility: The ability to choose the range of cells and ignore headers would provide users with more flexibility when working with spreadsheets.