collections: allow a collection to be set/mapped from a spreadsheet

[josephjclark]

I want to:

• define a bunch of mappings in a spreadsheet (XLS or gsheets)
• Pull those mappings into the CLI
• Upload them into a collection

This feels like it's something the CLI is very capable of doing.

If the sheet just has two columns, we can just pull them as key/value pairs. Otherwise we need to take a range (or I suppose two columns). We may need to know whether the first two rows are a header and can be ignored (although it's probably harmless to include a header in the map - it's just one redundant key)

The mapping should be a single JSON object uploaded as a key to a single value

So the CLI command is like:

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

Maybe we need extra CLI args for "parse items as spreadsheet" or "choose this range" or "ignore headers".

[josephjclark]

I'd love some help with this and it's a nice stand-alone challenge - but the spec is super rough right now and there are no links to docs to get a new dev started. If you're reading this and you'd like to help, this is a great little project. Please tag me here and I'll help you get going!

[josephjclark]

@SatyamMattoo Are you still looking for projects to contribute to?

[SatyamMattoo]

@josephjclark Sure! I would love to contribute! Do you have some deadline planned for this? I might take some time to complete this due a busy schedule.

[josephjclark]

Hi @SatyamMattoo , nice to hear from you!

No deadline as yet. No pressure either - I just thought it would be a fun one for you :)

I'll leave this opened and unassigned for now. Drop me a line here if/when you think you have time. Best to focus a few days on it. We'll set up a call and I'll talk you through it. You'll have to get up to speed with our new Collections feature - but after that I think it's quite straightforward, standalone and valuable.

[SatyamMattoo]

@josephjclark Thanks for considering me! I'll take some time this weekend to review the Collections feature, and we can discuss it on Monday if that works for you?

[josephjclark]

@SatyamMattoo Sure but only if you have time to commit to the work next week.

Book a slot in my calender here and we'll talk about it on Monday

[josephjclark]

• let's just support google sheets for now (no csv file or xls file)
• Use the googlesheets client. Check out our adaptor to get started with the API: https://github.com/OpenFn/adaptors/blob/64e0069c529fe774b58a28f480f43c9046d1df1a/packages/googlesheets/src/Adaptor.js#L210
• Oauth can be difficult, maybe only support public sheets for now? Later we'll work out how to get an access_token straight from the CLI
• Generally keep it simple for now and we'll build complexity in
• don't save collection to disk: generate it in-memory and upload straight to the app
• maybe a unit test?

endpoint: https://app.staging.openfn.org
project: satyam-test
collections: satyam-loinc and satyam-countries
testsheet

collections CLI docs: https://docs.openfn.org/documentation/collections-cli#uploading-items