I run a business where the staff need to fill out a weekly spreadsheet. I want to automate the duplication of this and make my involvement as minimal as possible, but I need to have remote access to it when needed. I also want to avoid giving staff access to the master file. We’re an iPad-only business.
Below is my current process — are there any steps I can simplify or remove my involvement from?
I have a Shortcut which duplicates the master file from my iPad and saves it into a Dropbox that accessible on the staff iPad. I’m reminded to run this each week.
Staff open this file and are instructed to add me as a collaborator, so I can view it.
One idea I’m wondering is whether it’ll be easier to keep all duplicates on my main iPad and simply send a collaboration link to the staff each week — but can this be automated in Shortcuts in any way? Also, currently relying on me to duplicate the file each week is a risk in case I’m ever incapacitated or don’t have access to do the Sunday duplication, it would cause an issue for the team). Would love to hear any ideas.
Note: I tried an alternative system where we simply shared access to the file directly within Dropbox, but this resulted in conflicts when multiple of us accessed it at the same time.
One question - what is the current issue you foresee with providing everyone with access to the “master file”? This is kind of an underpinning point as depending upon why, there may be options that negate the need for some of what is currently a challenge.
If it is access contention, there are many options around collaborative spreadsheet access - Google Sheets, Office365, Quip, etc. Similarly version control may be another option.
If it is that you want auditability and file versioning isn’t enough, then some sort of database that generates a spreadsheet compatible data source may be the “right” solution.
VIt’s because the spreadsheet handles cash and needs to be audited. I want to lower the potential for a member of staff to edit or change the spreadsheet and incur bugs or nefarious edits in future duplicates.
If it makes my life substantially easier though, maybe I could give them access to the master file and just check if the time stamp ever changes on it.
I think ideally I want to transfer this to an online system that is in a database and accessible remotely. Until then though, I’m just looking to keep this spreadsheet and easiest way to duplicate it for the time being. But maybe I’ve already found it?
Right, so if I understand correctly what you’ve said, you have an auditable spreadsheet for dealing with some financial aspect of the business and the master copy is stored on your iPad and you are not in a position to entrust your staff to update it independently, so you give them each a copy weekly, they share their copy with you, and presumably you collate that in some way.
If you really do have a master copy of your spreadsheet that only exists on your iPad, change that now. Hopefully it is backed up in some way, or at least sync’d to iCloud. If it is financially auditable, you’ll want archive copies and so you probably should look to have some sort of archival set-up in place if you don’t already have one.
Depending upon what type of financial information this is, you may well be better at looking at a system to track and manage the information. No amount of automation is ever going to give you the same piece of mind and security as a tailored solution.
If the master spreadsheet is on your iPad, the only way to send out copies is from your iPad. If the original gets hosted somewhere else, then you could look at ways to make a read only copy always available and so the staff could simply grab a copy when they need it. Alternately, you could use something like IFTTT or Zapier to send out a reminder e-mail with the link each week (maybe via a push to a Mailchimp mailing list?)
Depending upon what platforms you use, maybe you could set things up so that your staff have their own mini-master spreadsheets that feed into an overall master spreadsheet. e.g. in Google Docs, you could use the IMPORTRANGE function to do this. I think Excel/Office365 would only support this at a desktop level currently, but storing data in SharePoint lists can always jump you a few stages on around data capture and storage in a pinch.
Given this is a financial, auditable spreadsheet, I wouldn’t only go by timestamps for last updated. That gives you no audit trail whatsoever. Who updated it, when, and what they updated are usually the sorts of things you need to know for audit purposes. If you were to continue with that approach, I would suggest using some sort of version control where the spreadsheet is checked out, updated and checked in. Again the options come back to what sorts of platforms you are using and if you have any document management systems. If you were using Office365 and Sharepoint online for example, you can set a document library to apply version control; but there are many solutions out there.
I probably should have said earlier — the spreadsheets aren’t that sensitive — it’s to keep track of a till float during the week. So long as everything’s above board by the end of the week, the spreadsheet can be archived and the next week begins.
I think for now I may give the team access to a ‘mini-master’ spreadsheet, which I keep an eye on to see if it’s been edited, and they duplicate themselves manually or with a Shortcut.