Google Sheets Workshop
Advanced
Sharing Data Through Google Sheets
IASBO 2023 SPRING CONFERENCE
Questions to ask yourself as you start?
1) What data would be helpful to share? Administrators ask to see their budgets.
2) Can we export the data easily? We can export monthly, year-to-date, and previous
year account information into a csv/excel file from my accounting software.
3) Is there a way to identify the accounts that we want a specific Administrator to
see? In Software Unlimited (Flexible Financials/Report Writer), we can create a
report that shows account information by ACCOUNT GROUP ID.
● Spend time thinking about the information you want to include.
● Format the report so you can quickly download, copy, and paste with no additional
formatting.
● You need the account numbers in separate rows with the Account ID and
financials in separate columns (Budget, YTD, Prior Year, etc.)
Creating Shared Financials Thru Google Sheets
RESOURCE LINKS:
Google Sheets Advanced Drive
Instructor Notes
Exported Data (report from SUI included in the shared drive folder)
Financial Data Spreadsheet
Sample Shared Data Spreadsheet
“IMPORTRANGE” Function
Copy a range of data from one google sheet to another.
=IMPORTRANGE(spreadsheet_url, range_string)
Function requires TWO pieces of information from original google sheet:
1) The URL of the google sheet.
2) The sheet name (optional) and the range of cells to import.
Note: Use “ “ around both items. Use ! between sheet name and range.
Example:
=importrange("https://docs.google.com/spreadsheets/d/1M7MaeOXD-q1ZTVl6
LqbG5yp5KfHkDeMTd7tFlGk0z-Y/","Export!A:I")
“QUERY” Function
A QUERY allows you to perform database-type searching in Google Sheets, so you can
FIND, FILTER, and FORMAT data.
=QUERY(data, query, [headers])
● Data: The range of cells to perform the query on.
○ Use IMPORTRANGE to use data from another spreadsheet.
● Query: search to perform (Google Visualization API Query Language)
● Headers: Optional field to specific number of header rows at top of data.
Example: =query(IMPORTRANGE(Financial_Data,"FinancialData"),
"Select Col1,Col2,Col3,Col4,Col6,Col7,Col8,Col9,Col10 Where Col1 Matches
'"&AccountGroup&"' order by Col2",0)
Named Ranges (or Cells)
You can name ranges in Google Sheets to keep better track of them and create
cleaner formulas.
➔ Only contact letters, numbers, and underscores.
➔ No spaces or punctuation.
➔ Can’t start with a number, or the word “true” or “false”
“UNIQUE” Function
Returns unique rows in the provided source range, discarding duplicates. Rows
are returned in the order in which they first appear in the source range.
Example: =UNIQUE(A1:A100,false,false)
“TEXT” Function
Converts number into a specified text format.
=TEXT(number, format)
Number - The number, date, or time to format.
Format - The pattern by which to format the number, enclosed in quotation
marks.
Date Format Examples:
Data Validation: Dropdown List
Data validation rules allow you to set criteria that can be entered into a
worksheet cell. You can define one or more data validation rules for your
worksheet.
Protecting Sheets or Ranges
Prohibit changes to content in the spreadsheet by protecting the sheet or range.
Range: To protect a cell or group of cells.
Sheet: To protect the entire worksheet. If you want a set of cells to be
unprotected in a sheet, check the box next to "Except certain cells."
Google Sharing Settings
Viewer - can’t change or share the file. See next slide for setting to prevent
copying or downloading.
Commenter - allowed to make comments, but can’t change or share the file.
Editor - can make changes, accept or reject suggestions, and share the file.
Add Expiration - allows you to remove access after a specific date
Additional Sharing Settings