You can now take your custom report data from Workamajig into Google Sheets for further data manipulation. This is done by bringing over the CSV data of the reports URL.
Besides Custom reports, this also works with the Views from the global search and the Client profit & loss (multi-view) and Project profit & loss (multi-view). But no other standard reports at this time.
NOTE: If you have any issues with a report used, try copying the report and using its link.
Reset company & user tokens
In case you get an error when trying to copy the link to your clipboard, be sure to reset the token for your company and users.
This will mean if you currently use any other feature, like APIs that use these tokens. Those will need to be updated on your end as well.
Reset company token
Go to System setup > Connections > API. From there, click on Generate new API access token.
This will generate a new system token.
Reset user token
Click on your name in the upper right. Then click on the blue link under API user token.
Then click on New user token.
Copy Google Sheet link
From your report, set any prompt filters> SEARCH (this sets the filters into the "link">click on Print > Copy Google Sheet Link.
This will copy the CSV's URL to your clipboard.
Paste link in Google Sheets
Paste this link into the upper left cell of the Google Sheet.
You will then see the sheet automatically populate with the report data.
- The field you paste into is then relabeled with the first column header of your report.
- If you have any calculated columns in your report, they become the first column(s) no matter their placement in Workamajig.
- All calculated columns come over with their columns reference ID (ie. Calc_Column_1) and not the caption given to them in Workamajig.
- Dates may look odd at first, so you will need to format those columns after pasting.
Format dates in Google Sheets
When the data is first brought into the Google Sheet, all the formatting is set to automatic. This means columns with date values need to be formatted in the sheet itself after pasting. Even if you use an advanced calculated column to format the date differently in Workamajig.
To update the formatting of a date column, for example. Click on the column header to highlight the entire column. Then click on Format > Number > Date. Or Custom date and time for more advanced ways of showing the date.
Auto-refresh the data
You can also establish an auto-refresh of the report data automatically. Keep in mind there is a max refresh of only once per hour. Even though you can choose 'once per minute'.
From the Google Sheet, click on File > Settings.
Then click on the Calculation tab. In here, click on the Recalculation drop-down. And choose On change and every hour.
Your report will now auto-refresh every hour.
Share your creations
Are you a Google Sheet guru, or found a neat trick to present the data? Then why not share it with your fellow Workamajig colleagues.
NOTE: You must request a login to join the Community section.