Data Management Excel Tooling

This repository contains proof of concept code for explorations of how to use Excel as a data management tool. The premise is that we try to use Excel as the interface to data base. Since Excel is a already well known tool in the administration.

See also the subfolder /add-in/et/ for a proof of concept for an Excel add-in that can be used to manage data in a database. This is actually the more promising way to handle this. It allows to have a more fine grained method of user authorization and allows to use modern web development tools to build the interface. The example add-in you can find there is the same as the scripts under /office-scripts/ but with a more modern interface.

Prerequisites

• Office 365 License
• optional Sharepoint
• Supabase Account
• Node.js

Usage

• Create a project on supabase and get your project id, supbase api url and service role key
• Use the supabase CLI to deploy the local migrations to supabase.com

supabase login
supabase link --project-ref <YOUR SB ID>
supabase db push

• Populate the database with some data
• Add your service role key and supabase url to the top of the scripts in /office-scripts/populate-worksheet.ts and /office-scripts/send-worksheet.ts.
• Open a blank Excel workbook and add the scripts to the workbook automate ribbon as new scripts

Execute the populate-worksheet.ts script to populate the worksheet with data from the database. Execute the send-worksheet.ts script to send the worksheet to the database.

Frontend

In the folder /frontend you will find a Next.js application where you can watch the changes made to the database in realtime. Follow the README.md in that folder to learn how to set it up and connect it to your database.

Development

• We use direnv to setup environment variables. You can use the .envrc.example file as a template for your own .envrc file.
• Here are some resources for learning about office scripts.
• https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel

You will have to edit and develop the scripts within Excel. There seems to be no way to use an external editor.

In the folder xlsx you will find a sample Excel file that you can use to test the office-scripts and a script /xlsx/import.js that you can use to populate the database. See the /xlsx/README.md for more information.

Contributing

Before you create a pull request, write an issue so we can discuss your changes.

Contributors

Thanks goes to these wonderful people (emoji key):

This project follows the all-contributors specification. Contributions of any kind welcome!

Credits

Made by

A project by

Supported by