Google Sheets: How to Import a whole range of sheets and the whole 9 yards....
In the length of the time I stayed with CC I had created (for myself and for my team) a dashboard which shortcuts information from a separate dashboard into mine for sorting and viewing purpose. I've utilized most of the common Google Sheets command to achieve this such as IMPORTRANGE, FILTER, VLOOKUP, and used Conditional Formatting, and so on.
IMPORTRANGE
This command is great when using a range of data from another sheet and you want the same data to be dynamically "mirrored" to your spreadsheet. you would need to know two things, the speadsheet link that you are to import and the range of the data you want to import.
so in other words,
IMPORTRANGE(spreadsheet_key, range_string)
Example
IMPORTRANGE("abcd123abcd123", "sheet1!A1:C10")
Summary
Imports a range of cells from a specified spreadsheet.
spreadsheet_key
The long sequence of characters in the "key=" attribute of the URL. In the new Google Sheets, use the entire URL.
range_string
A string, of the format "[sheet_name!]range" (e.g. "Sheet1!A2:B6" or "A2:B6") specifying the range to import.
Having this done, i was able to have my data dynamically changing from the parent Sheet. I worked on this via creating a Pivot Table according to what I need.
Pivot Tables
Using a pivot table you would be able to sort the data you would want to have in a table.... that you can pivot... thus the name.
So using a pivot table I am able to get what I want to get in the data I'm ask to have, say for instance, know the score of specific team members on a given duration, like Month to Date, Week to Date and Daily.
FILTER
The Filter command is used when you want to just filter a specific group or person in a Sheet.
FILTER(range, condition1, [condition2, ...])
Example
FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
Summary
Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions.
range
The data to be filtered.
condition1
A column or row containing true or false values corresponding to the first column or row of range, or an array formula evaluating to true or false.
condition2... - [optional] repeatable
Additional rows or columns containing boolean values TRUE or FALSE indicating whether the corresponding row or column in range should pass through FILTER. Can also contain array formula expressions which evaluate to such rows or columns. All conditions must be of the same type (row or column). Mixing row conditions and column conditions is not permitted.
Using this allowed me to do a vlookup on the data that I need such as showing the list of cases, CSAT's and DSATs on a specific agent.
Creating a Trending Scorecard
Creating a trending scorecard is somewhat tricky, you need to have a list first of the scores of the subject you wanted to have the Chart with. So we'll be using a sheet of raw data first, in my case the Filtered List that I have created for a different purpose.
Use a Pivot table first and filter by closed date.
then creating a manual table and add four more columns. CDSAT,CCSAT, total and Average.
The key here is to make the count of the CSAT appear per day and call it via vlookup. So each day you have to count the previous score plus the current score on the column then add the average.
ex.
=if(isblank(date),"",previous CCSAT+Current CSAT)
so the result would be...
EXTRA:
Calculating CSAT Score
You just have to divide the total CSAT to the total survey count(both CSAT and DSAT).
CSAT Needed
to compute for CSAT needed to reach a certain amount say 90%, what you need to do is to multiply the DSAT score by 10 minus the number of Returns. You can also add an if statement here that if the result is less than 0 then say 0 as a result.
