| FOLIO Reports Google Sheets Add-In | Michelle Suranofsky | Michelle will share her work on the FOLIO Reports Google Sheets Add-On (also on GitHub) - Querying FOLIO directly using APIs
- built on queries developed for OLE, quick way to get them up and running in FOLIO
- Lehigh built a sheets add-on a few years ago; since it can talk to APIs, see it as a nice option for reporting
- Once you create a script, you can set it to refresh at a particular time (e.g., 5am), and you can also send an email each morning with a link to the spreadsheet
- Scripts can also create files
- Cons:
- scripts run on Google servers, so FOLIO needs to allow the Google servers to make queries
- Google has caps for how many API calls it can make; Lehigh has a business account and is a small school, so they never hit the caps, but another school might
- There is a script time limit, so not great for long-running scripts (though there may be workarounds)
- How to create:
- A spreadsheet itself can have a script (Tools > Script Editor); can write and run script from the editor, and the script is attached to that specific spreadsheet. This is how most of Lehigh's reports are built. This means there is no development environment; you can just write the code right in the spreadsheet and share it as needed.
- Can also use Google's concept of "Add-ons"; from any sheet, go to Add-ons > Get Add-on. Google lets anyone publish scripts as add-ons for others to use. Can publish either to entire world or just within your organizations. Your add-ons show up in the add-on menu. Can add the add-on to multiple sheets.
- Can you start as a script in a spreadsheet and move it to an add-on?
- Yes. Lehigh is creating an inventory application that started as a script in a sheet, but they found they would need to spread it across multiple sheets, so it became an add-on.
- Michelle has published a basic FOLIO Reports add-on (also on GitHub, along with invoice-creator and lehigh-reports).
- Set one, set up FOLIO connection
- From menu, select your report
- pop-up shows that report is running, can take a minute or two
- asks API for 50 rows at a time, pop up shows progress
- it's the main way Lehigh has developed reports since going live with FOLIO
- can push new reports to the add-on and it gets updated for staff when they open the add-on next
- also just have sheets with report scripts that get shared with staff; authentication has been saved as a library, which is not viewable in the script itself, but the rest of the script is viewable
- Scripting syntax is very similar to vanilla JavaScript, so if you have any background in basic programming (e.g., php, JavaScript), should be straightforward.
- Also have a concept of a library, so could use the same script in multiple reports.
- Questions:
- have the reports running against the APIs caused any performance issues in the FOLIO system?
- scripts run overnight, but even during development of the scripts, didn't hear about any slowdowns
- how many people developing reports?
- just Michelle
- goal was to recreate reports people were used to having with OLE, now sort of just on autopilot
- previously had a script running that would send out an Excel file once a week, think this nicer
- does Lehigh use Google for email, calendar, etc?
- if you're using Office 365, there are automation tools that might offer similar functionality
- Interested in how the source code is managed; how do you move the script between the sheet and GitHub?
- once the script is developed, doesn't really get changed frequently. have them in GitHub. there are some tools where you can push to GitHub and GitHub can push to Google, but haven't tried that.
- Is Lehigh self hosted?
|
Announcements /
Reminders | Angela | Recruiting New Query Developers - The Reporting SIG is always on the look-out for new query developers. Please let us know if you are interested in doing query development or if there are others at your institution who might be a good fit.
Test Data FOLIO Reporting developers and Reporting SIG members are encouraged to use this new page to share test data cases they have entered into FOLIO reference environments to keep us all aware of what data to expect when we test our queries.
Cluster Ranking New Report Clusters are added on a regular basis, so it is important to make sure your institution is reviewing these clusters and ranking them to establish report development priorities. If you rank reports for your institution, please follow the instructions below. If someone else ranks, please pass this information along to that person so your institution's vote can be included. - Action =>> Please review Reporting SIG-All Report Clusters (57 issues) in JIRA and RANK each report cluster for your institution (R1-R5)
- For reporting, institutions only need to rank the UXPROD Report Cluster JIRA issues. All reporting requirements, which are captured in REP-XXX issues, roll up to the UXPROD Report Clusters. Report clusters cover one or more report (REP-XXX issue) requirements.
|
| Updates and Query Demonstrations from Various Reporting Related Groups and Efforts | Community & Coordination, Reporting Subgroup Leads | Project updates Reporting development is using small subgroups to address priorities and complete work on report queries. Each week, these groups will share reports/queries with the Reporting SIG. Reporting development team leads are encouraged to enter a summary of their work group activities below. RA/UM Working Group - Worked on testing out some query writing in metadb, otherwise just trying to finish up Patron List query
- Will have two more meetings and then a two week vacation
- Context
MM Working Group - The Product Council discussed Data Integrity and data flow at the last meeting. There's a suggestion to limit new development to resolve these issues. A task force has been created with this draft charge.
ERM Working Group
Reporting SIG Documentation Subgroup - Feedback period is over, will review feedback and send documents to DWG for review and incorporation
- Additional Context
RM Working Group - discussed impact of item data model changes on RM queries
- new data model to be included in Juniper (mid August)
- new bound with table, see UXPROD-1241 for details
- plan to analyze impacts to each RM query
- working on queries for the 1.2 FOLIO Analytics release
- reviewed next steps for invoice queries
- reviewed standing orders query for ERM and RM
- for latest updates, see RM Prototype and Query Development Status
External Statistics Working Group - no updates currently
- ACRL query set included in FOLIO-Analytics 1.1 release
- new organizational/tracking scheme for JIRA, with pointers to queries in folio-analytics repository
- New organizational structure for External Statistics reports
- external statistics reports (e.g., ACRL) typically require running queries from different functional reporting areas
- these reports will be captured in JIRA under one UXPROD-XXXX report cluster issue, then the descriptions will point to each of the queries required to run them on the folio-analytics repository
- institutions will need to rank each of these 8 new UXPROD-XXXX report cluster issues
- each reporting development team will take responsibility for the queries in their area for the external statistics clusters
For all recent work on FOLIO Reporting SQL development:
|