I am completely new at scripting and am looking for some help with the below problem we are trying to solve.
Each week we get a list of staff names that have worked in a certain location in Excel, and have to manually link these up in Airtable and I am looking for a way to automate this using a script. The key part is we use this data to work out who has worked where in the last 12 weeks.
I’m thinking we can use an internal count to keep track of how many weeks ago it was they worked there, which is reset each time they work at the location, or 1 is added each week they don’t.
All staff names are linked records which exist in a table, though some names are different between the Airtable and Excel records, and staff can work at more than one location.
The rough idea I have is as below:
- Search for location
- Add 1 to count for each staff member already listed at location
- Search for staff name at location
- If present, reset count to 1
- If not present, add staff name, create new count for that staff name at that location, set count to 1
- Loop until theres no more data
I would like for this to be automatic after providing the data, at the push of a button. We can upload the Excel sheet onto Google Sheets and use that API, its just the actual processing of the data I am stuck with