WORK IN PROGRESS
This project is a simple example of how to automate accounting with Gmail, BigQuery and Apps Script. It automatically searches e-mails containing invoices and purchase receipts, based on search criteria set by the user per each merchant that needs to be tracked. The data is then extracted, categorized and saved to a spreadsheet in Google Sheets or a BigQuery table.
Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications for free.
Resources to get started with Apps Script:
When running in Apps Script, this application uses the Properties Service to store configuration data.
When running locally, the application uses environment variables to store configuration data in place of the Properties Service. Since Properties Service separates Script, User and Document properties in different scopes, the following prefixes must be prepended to the names of the environment variables to recreate the same behavior:
| PROPERTY SCOPE | PREFIX | EXAMPLE |
|---|---|---|
| Script | GAS_SCRIPT_ |
GAS_SCRIPT_MY_SCRIPT_PROP=... |
| User | GAS_USER_ |
GAS_USER_MY_USER_PROP=... |
| Document | GAS_DOC_ |
GAS_DOC_MY_DOC_PROP=... |
| PROPERTY SCOPE | PROPERTY NAME | ENV VAR NAME | DESCRIPTION |
|---|---|---|---|
| User | GCP_PROJECT_ID |
GAS_USER_GCP_PROJECT_ID |
ID of the GCP project hosting the app. |
| User | BQ_DATASET_ID |
GAS_USER_BQ_DATASET_ID |
ID of the BigQuery Dataset. |
| User | BQ_DATASET_LOCATION |
GAS_USER_BQ_DATASET_LOCATION |
GCP zone of the BigQuery Dataset. |
| User | BQ_TABLE_EXPENSES_ID |
GAS_USER_BQ_TABLE_EXPENSES_ID |
ID of the BigQuery table. |
| User | GMAIL_LABEL |
GAS_USER_GMAIL_LABEL |
Label to append to Gmail messages that were already processed. |
| User | PROCESSING_BATCH_SIZE |
GAS_USER_PROCESSING_BATCH_SIZE |
# of messages per merchant to process at every run. |
Some of these settings have default values, as specified in the
src/lib/config.js file.
Use the main function from src/main.js
to run the application in Google Apps Script.
Run:
bin/startor:
node bin/startto run the application standalone as a Node.JS application.
This project is licensed under the MIT License, see the LICENSE file for details.
As stated in the MIT License attached to this project, this code is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. USE AT YOUR OWN RISK.
