This thread tickled my problem-solving brain, so I’ve run up a tool that should do this now in Google Apps Script and I’d welcome people trying it out. I don’t claim this is “production quality” code but it seems to work for me in the common cases I’ve tried and I’m happy to put it out there for feedback.
Instructions
- Create two empty folders in your drive (named whatever you like). One will be the “source” folder that the script scans to find receipts to upload, the other will be the “processed” folder where items are moved once they have been imported. The processed folder can be a child of the input folder if you want - the scan only looks at the source folder itself and doesn’t descend into child folders.
- Open this Google spreadsheet, then do “File → Make a copy…” to save a copy to your own Google Drive (you can’t edit the original). You will be entering sensitive details into this sheet in step 4 so make sure you don’t save it in a shared folder.
- In your QuickFile account, go to account settings → 3rd party integration → API, and create a new app.
- you can put anything you like for the name and description, I recommend something meaningful like “GDrive importer”
- grant the
Invoices.Document_Uploadpermission - this is the only permission the app requires
- In your copy of the spreadsheet, enter the required configuration settings (account ID, API key and App ID - see the image below) in the appropriate cells. Make sure you don’t add any leading or trailing spaces around any of the values.
- Use the “select” buttons to select the two folders you created in step 1.
- the first time you click the button it will ask you for authorization. It will tell you the app isn’t verified, you need to click “advanced” and then “go to Drive-to-QuickFile-script (unsafe)”, and allow the permissions it is asking for:
- full access to your google drive (to read the files to upload and to move them to the processed folder when finished)
- access to spreadsheets (to read the configuration settings from this sheet)
- connect to an external service (to call the QuickFile API)
- run when you are not present (to be able to work in the background)
- display and run third party content (for the drive folder picker UI)
- the first time you click the button it will ask you for authorization. It will tell you the app isn’t verified, you need to click “advanced” and then “go to Drive-to-QuickFile-script (unsafe)”, and allow the permissions it is asking for:
- The script is able to convert Word, Excel and OpenOffice files into PDFs before uploading them to QuickFile - this is not a perfect conversion as it works by first importing the Word file as a Google Doc and then turning that into PDF, so you can untick the box if you would prefer these kinds of files to be uploaded in their original format (though you may not be able to view them in the receipt hub). JPG and PNG files are uploaded as-is, native Google Docs are always converted to PDF.
Once everything is set up, you will see a “QuickFile” menu appear in the menu bar at the top of the spreadsheet. In this menu you can “Import now” to run a one-off import and check everything is working as it should be. Once you’re happy with this, select “Enable trigger” to start the background job that will poll every ten minutes. After that you can close the Google Sheet, the import script will continue indefinitely in the background.
Any programmers who are interested in looking over the code, it’s all there in “Tools → Script editor” - any suggestions welcome. If there’s interest I’d be willing to look at turning this into a proper Google Sheets “add-on” rather than an unverified script, but timed triggers in add-ons are limited to fire no more than once per hour, which means there’d be a much longer delay between dropping something into Drive and having it appear in the Receipt Hub.
API credentials
The API apps page can look a bit intimidating if you’re not a programmer, the bits of information you need are highlighted here:
