You can connect a template to a log spreadsheet. When users use the template, each submission will be linked to the log to allow easy organization and access. This tool is especially useful in the case of expense reports, given that it compiles reports submitted by many users. In the following guide, we will guide you through utilizing the Expense Report template and connecting it to a ledger spreadsheet.
Following the same process, copy the Database Ledger to My Templates. Return to the Public Templates page and search for Company Report Ledger and save it to your My Templates.
Now that the templates are in your Templates page, we will need to connect it to the ledger.
After selecting the spreadsheet you want connected to the expense report template, you should see a drop down menu appear. These drop down menus contain process information variables which collect data based on each variable. You can choose to add more than one process information variable by clicking on the + icon.
In this case, we will need the following process variables:
Since we also want to keep track of the Total Expense, we will add another Process information variable. To do this, scroll down the list and click the + button. In the dropdown menu, select columns in process and in the following popup select the cell that contains the total estimated amount, in this case it is found in cell G23.
Click Run Test to send test data to the spreadsheet ledger to make sure the connection is working. You should see an alert saying the data was successfully copied. To verify the test data was successfully sent, click on the spreadsheet ledger and in that spreadsheet look for a second sheet titled CV-Process-Log. You should see the process information variables in the first row of that sheet.
To start, open the Database Ledger spreadsheet in Google Docs Spreadsheet. In our Database Ledger spreadsheet, there are four columns Date, Employee Name, Approval Status, and Total Amount.
We want the data from CV-Process-Log to show in our Database Ledger . To do this we will use the formula =INDIRECT(“‘CV-Process-Log’!G”&ROW(P2)) It is important to note that P2 is the first cell that will contain saved dates in CV-Process-Log. This formula will be added to cell B6 because Column B is where our saved dates will show in the Database Ledger. Next we want to click on the cell and click the little square in the bottom right corner and drag it all the way down to the bottom of the Database Ledger.
Now we will want to do the exact same for Employee Name, Approval Status, and Total Amount. Below are the formulas you will need. Remove the brackets when inserting the formula to the Database Ledger
NOTE: Remember to drag the data explained above so that each cell in Database Ledger as a formula.
Now to see if our formulas are working, we will click Run Test in the Template edit page like we did above. After running the test, you should see test variables in the Database Ledger”.
Once you confirm you see the test variables, you are all set! Whenever you submit the template Expense Report the data of that process will be saved at CV-Process-Log sheet and inserted to the main spreadsheet of the Database Ledger.
Connecting Expense Report To Ledger
expense report
use case
