Top
Top
  1. Workamajig
  2. Roles
  3. Accounting
  4. In-Depth Guides for Accounting

Articles in this section

See more

Google Financials - cell style [in-depth guide]

Google Financials provide an alternative to Excel Financials for those using Mac OS or do not have Excel installed on their Windows machine.

Google Financials requires that you setup a Google account. This method will use a Google Spreadsheet to provide the data.

NOTE: Google spreadsheet limits the number of queries that can be made per month per worksheet. This is set to 1000 queries. This is something that Google has set. We have no control over the number of queries they allow. A query is considered anytime a spreadsheet cell goes to an outside database link to get information.

Workamajig has created 2 methods of setting up Google Financials:

1) Report style- allows you to create a report in Workamajig, use this report to provide the necessary information, then use the spreadsheet function to perform further calculations. Uses 1 cell

2) Cell style - allows you to set up individual worksheet cells that pull in information from Workamajig into the spreadsheet. You can then use the spreadsheet functions to perform further calculations. Uses 1 cell per data point

At this time, we recommend that you set up the report style. This will help reduce the potential of running into the 1000 query cap. Each time that a worksheet refreshes, any cell that contains a query will be run and count towards the 1000 query cap.

Cell style

The Google Financials-Cell style is a separate type of report building from the report style. You cannot use the functions that you see below to edit report-style cells. Each cell in the 'cell style' is considered a query by Google. Please be aware that an excessive number of cells may cause the report to fail due to Google's 1000 query cap.

Setup

You will need the URL that you use to log in to Workamajig and the authorization token, located in Workamajig via clicking on your name in the top right corner > along the left side, Login info > Auth token.

Authtoken.jpg

If you do not see an Authentication token, or need to reset at a system level: Menu > Admin/manager > System settings > Account information: Connections > API > click Generate system-wide user tokens. There is not an individual reset of this token.

 

NOTE: If you do not have access rights to financial information in Workamajig, you will not be able to view any results.

This method uses your Workamajig website URL with an added string to display results in the spreadsheet cell in the following format:

website URL/services/googlefinancials.aspx?parm=value&parm=value 

example URL (substitute your Workamajig URL for the one provided)

=importData(“https://app#.workamajig.com/services/googlefinancials.aspx?
auth=kj2k32&action=getamount&accountstring=10200,10444&
StartDate=1/1/2011&EndDate=12/31/2011”)

You can also use the concatenate function to place data from other cells into the formula.

=importData(CONCATENATE("https://appXXX.workamajig.com/Services/
googleFinancials.aspx?auth=",$B$1,"
&Action=getamount&AccountString=",C17,
"&StartDate=",text($D$13,"mm/dd/yyyy"),
"&EndDate=",text($D$14,"mm/dd/yyyy"),"&BalanceType=C"))

This is basically a comma-separated list of items to put together into one big string.

You can use the concatenate(function to reference parts of the URL to other cells). Consult google’s help guide for assistance on this.

Parameters:

  • auth:  (Required)
    The users encrypted authentication token. This will be visible on the My settings page.
  • action: (Required)
    The function you want to call, choices are
    • GetAmount
    • GetBudget
    • GetNetIncome
    • GetRetainedEarnings

Parameters by Action

  • GetAmount
    • AccountString
      The GL account string is a comma-separated list of account numbers. You can also include ranges of accounts using a :
      Ex  10200,10300,10400,2000:4000
    • BalanceType
      D for Debit, C for Credit. Defaults to D
    • StartDate
      The starting date you want. defaults to 30 Years prior.
    • EndDate
      The ending date you want. defaults to 30 Years in the future.
    • ClassID
    • Department
    • Office
    • ClientID
    • ProjectNumber
    • CashBasis
      Add 1, Yes or True for Cash Basis otherwise leave it out for Accrual
    • CompanyName
      The GL Company Name
  • GetBudget
    • AccountString (Required)
      The GL account string is a comma-separated list of account numbers. You can also include ranges of accounts using a :
      Ex  10200,10300,10400,2000:4000
    • ClassID
    • Department
    • Office
    • ClientID
    • CompanyName
    • BudgetName (Required)
    • StartingMonth
      1 - 12 Defaults to 1
    • EndingMonth
      1 - 12 Defaults to 12
  • GetNetIncome
    • AsOfDate (Required)
      The system will get the net income for the fiscal year based on this date.
    • ClassID
    • Department
    • Office
    • ClientID
    • ProjectNumber
    • CompanyName
      The GL Company Name
    • CashBasis
      1 for Cash Basis, 0 for Accrual Defaults to 0
  • GetRetainedEarnings
    • AsOfDate (Required)
      The system returns the retained earnings as of this date.
    • ClassID
    • Department
    • Office
    • ClientID
    • ProjectNumber
    • CompanyName
      The GL Company Name
    • CashBasis
      1 for Cash Basis, 0 for Accrual Defaults to 0

For an example of this in a Google Spreadsheet format, click HERE.

Related articles