If you are as much of a basketball fan as I am then you are getting excited for March Madness!! But since this is a Blog devoted to Google Docs, I guess I should connect it someway. Bracketology anybody? I'm currently working on a Google Docs based bracket that will take advantage of the =googletournament() function.
I'm working on two versions. One version will be set so that you and your friends can compete against each other and have multiple brackets in a pool. The other version is a single bracket so you can keep track of your own score!
Currently, features include automatic scoring, validated team choices so you can only choose the available teams for that certain game, customizable options, a standings view and information about each team and game!
The biggest hurdle is filling out the bracket. Due to the current limitations of forms, a form input is just not feasible. Filling out the bracket will require each of your team members to fill out their own bracket, or you can share only their bracket using tab level permissions in spreadsheets.
Don't worry; there will be plenty of instruction on how to use it. But stay tuned!
If you have any recommendations for features that you would like to see on the bracket please put it in the comments!
Cheers!
Saturday, February 25, 2012
Monday, February 13, 2012
New Feature: Sparklines
Ever wanted to quickly see a trend in your data? Want to add a quick small graph to a UI? Now you can with Sparklines! Sparklines are a quick and dirty way to see a trend in your data IN a cell. We can create quick line graphs and bar graphs using Sparklines.
See my example below that looks at average monthly temperatures for various cities in Nebraska, USA.
Note that you can change the background color and line color using the font/background color buttons from the editing toolbar. I also created a little tool that quickly generates the sparkline for a given city using the drop down. Enjoy and post any questions in the comments section!
To play around with this feel free to access this spreadsheet using the link below. Make sure you go to File>Make a copy in order to see formulas and use the drop down feature of the city chooser tool.
Sparklines Example Spreadsheet
Also, you will see below that the embedded Spreadsheet does not show Sparklines due to a current issue with Publishing and Exporting. Please see this post for updated information about the issue. To see the Sparklines click on the link above. Below the embedded Spreadsheet is a screenshot of what it looks like.
Cheers!
Screenshot:
See my example below that looks at average monthly temperatures for various cities in Nebraska, USA.
Note that you can change the background color and line color using the font/background color buttons from the editing toolbar. I also created a little tool that quickly generates the sparkline for a given city using the drop down. Enjoy and post any questions in the comments section!
To play around with this feel free to access this spreadsheet using the link below. Make sure you go to File>Make a copy in order to see formulas and use the drop down feature of the city chooser tool.
Sparklines Example Spreadsheet
Also, you will see below that the embedded Spreadsheet does not show Sparklines due to a current issue with Publishing and Exporting. Please see this post for updated information about the issue. To see the Sparklines click on the link above. Below the embedded Spreadsheet is a screenshot of what it looks like.
Cheers!
Screenshot:
Sunday, January 29, 2012
Google Doc Privacy Scare Explanation
*I've made a few changes to the post and they are highlighted in yellow.
Before this gets out of hand I wanted to explain the issue and explain why there is no reason to question the security of Google Docs. The feature explained is intentional and required for Google Docs to be fully collaborative but there is a minor problem.
Issue
There have been a few reports recently on the Google Docs Help Forum that unrecognized collaborators are appearing on Google Docs with sharing settings set as Private. Additionally, this Doc has been shared explicitly with other Google Docs accounts or email addresses not connected to a Google account. Users are worried that since unrecognized collaborators are appearing on Private Docs, their security is severely undermined.
Note: This only happens with Private Docs that have been shared at any time. This will not happen with Private Docs that have never been shared.
Terms
Understanding a few terms for Google Docs will help understand the intricacies of the issue and hopefully understand why Google Docs is still safe and the proper steps to take to insure complete understanding of security.
Sharing Settings - Umbrella term to define how permissions are doled out (this include Private, Anyone with the link, etc.)
Editing Permissions - Permission to Edit a Doc
Visibility Permissions - Permission to assign who can access the Doc
Google Account - an account that can access and create Google Docs (this can include a gmail account, a Google apps account, or an account under another domain that is used as the username to access Google Docs. Ex. I have username@hotmail.com as my Google Docs account and I sign in at docs.google.com with the username username@hotmail.com and my password that I set up for Google Docs that does not have to match my password for my hotmail email address)
Non-Google Account - an email address that has never accessed Google Docs or would not be recognized when trying to log into Google Docs via docs.google.com or another Google Apps domain (this could also be username@hotmail.com but only if I've never accessed Google Docs or tried to sign up with Google Docs using this name)
Background
When you create a Google Doc the default Sharing Settings are "Private", the default Editing Permissions include only the owner (you) and the default Visibility Permissions are "Editors are allowed to add people and change permissions." This means that when you share this Doc with someone as an Editor, this person can share it with anybody.
One way to do this that might seem wrong is when a Doc is shared with someone who uses a Non-Google Account. This will show up in the Sharing menu with the email address and three little dots above a line that looks like either three people in a group or a crown of some sort. If you choose to send a notification to this person, the notification include a link to the Doc as an invitation. This is a blanket invitation that is generated that will allow a Non-Google Account to access the Doc. Let us say that this person decides not to access the Doc but forward it on to their friend who has a Google Account. The email is forwarded and the invitation is unaltered. Therefore, the Google Account user can click on the invitation and access the Doc. This user seemingly accessed the Private Doc without the permission of the owner. But remember, this permission was not needed because Visibility Permissions are set so that editors can invite anyone! In this case, the Non-Google Account was invited as an editor and chose to allow the Doc to be accessed by someone else. This is no different from an editor with a Google Account that was explicitly defined by the owner, sharing the Doc with another Google Account holder.
Why this isn't an issue
The explanation above shows no violation of security based on the default settings assigned to the Doc. To keep this from happening the owner must change Visibility Settings to allow only the owner to choose who has access to the Doc. This is done at the bottom of the Share menu circled below.
When this Visibility Setting is chosen, a notification will not be sent to Non-Google Accounts even if you try. This keeps the Visibility Setting consistent and your Doc secure.
Why this is a problem
Currently, when an owner chooses to share a Doc to a non-Google Account email address regardless of the visibility settings the notification email sent to the non-Google Account can be forwarded and access by anyone who clicks on the notification link. This is a violation because the owner is not explicitly defining the user who received the invitation via the forwarded message. This violates the visibility setting where the owner has control over who can allow access to the Doc.
Before this gets out of hand I wanted to explain the issue and explain why there is no reason to question the security of Google Docs. The feature explained is intentional and required for Google Docs to be fully collaborative but there is a minor problem.
Issue
There have been a few reports recently on the Google Docs Help Forum that unrecognized collaborators are appearing on Google Docs with sharing settings set as Private. Additionally, this Doc has been shared explicitly with other Google Docs accounts or email addresses not connected to a Google account. Users are worried that since unrecognized collaborators are appearing on Private Docs, their security is severely undermined.
Note: This only happens with Private Docs that have been shared at any time. This will not happen with Private Docs that have never been shared.
Terms
Understanding a few terms for Google Docs will help understand the intricacies of the issue and hopefully understand why Google Docs is still safe and the proper steps to take to insure complete understanding of security.
Sharing Settings - Umbrella term to define how permissions are doled out (this include Private, Anyone with the link, etc.)
Editing Permissions - Permission to Edit a Doc
Visibility Permissions - Permission to assign who can access the Doc
Google Account - an account that can access and create Google Docs (this can include a gmail account, a Google apps account, or an account under another domain that is used as the username to access Google Docs. Ex. I have username@hotmail.com as my Google Docs account and I sign in at docs.google.com with the username username@hotmail.com and my password that I set up for Google Docs that does not have to match my password for my hotmail email address)
Non-Google Account - an email address that has never accessed Google Docs or would not be recognized when trying to log into Google Docs via docs.google.com or another Google Apps domain (this could also be username@hotmail.com but only if I've never accessed Google Docs or tried to sign up with Google Docs using this name)
Background
When you create a Google Doc the default Sharing Settings are "Private", the default Editing Permissions include only the owner (you) and the default Visibility Permissions are "Editors are allowed to add people and change permissions." This means that when you share this Doc with someone as an Editor, this person can share it with anybody.
One way to do this that might seem wrong is when a Doc is shared with someone who uses a Non-Google Account. This will show up in the Sharing menu with the email address and three little dots above a line that looks like either three people in a group or a crown of some sort. If you choose to send a notification to this person, the notification include a link to the Doc as an invitation. This is a blanket invitation that is generated that will allow a Non-Google Account to access the Doc. Let us say that this person decides not to access the Doc but forward it on to their friend who has a Google Account. The email is forwarded and the invitation is unaltered. Therefore, the Google Account user can click on the invitation and access the Doc. This user seemingly accessed the Private Doc without the permission of the owner. But remember, this permission was not needed because Visibility Permissions are set so that editors can invite anyone! In this case, the Non-Google Account was invited as an editor and chose to allow the Doc to be accessed by someone else. This is no different from an editor with a Google Account that was explicitly defined by the owner, sharing the Doc with another Google Account holder.
Why this isn't an issue
The explanation above shows no violation of security based on the default settings assigned to the Doc. To keep this from happening the owner must change Visibility Settings to allow only the owner to choose who has access to the Doc. This is done at the bottom of the Share menu circled below.
 |
| Choose "Change" to change Visibility Settings |
 |
| Choose "Only the owner can change the permissions" |
Why this is a problem
Currently, when an owner chooses to share a Doc to a non-Google Account email address regardless of the visibility settings the notification email sent to the non-Google Account can be forwarded and access by anyone who clicks on the notification link. This is a violation because the owner is not explicitly defining the user who received the invitation via the forwarded message. This violates the visibility setting where the owner has control over who can allow access to the Doc.
Conclusion
As the owner of a Google Doc you have complete control over who has access to the Doc owned by you. The only way One way around this is the paragraph explanation above or if someone makes a copy or takes a screen shot of your Doc. You are then no longer technically the owner of any new Doc and the new owner can do as they please. This is no different from someone making a photo-copy of a piece of paper or making a copy of a file and attaching it to an email to someone else.
I hope this helps clear up confusion but most importantly puts you at ease that your Google Docs are indeed safe if you make them!
Cheers!
Thursday, January 12, 2012
New Year, Cleaner Docs List
Do you organize your Google Docs based on cyclical activities such as school semesters or business quarters? Have you tried using Collections paired with the "Don't show in Home" feature of Docs? Try it!
The idea is that you want to keep these Google Docs but you don't want them showing up in Home. The goal is somewhat similar to the "Archive" feature in Gmail. This is for those who like a clean Docs List.
For students, a new year means new classes which hopefully means new Google Docs! But first let's clean the existing Docs List. Let's take this from the student's standpoint.
The Cleaning
First off, we need to create little bins for last semester's Docs. If you haven't already, I suggest creating a collection for each class. Click on the Create button and choose Collection.
Next, name the Collection.
You're done! Now, we must find the Google Docs to put in our new Collection. Find these Docs, select their check boxes, choose More and then Organize.
Now these Docs have been "Archived." They will no longer show up in Home even if they are edited by you or other collaborators. This is different from Gmail, in that if someone responds to an archived message it will reappear in your Inbox.
This method can easily be undone by completing the last step again. "Don't show in Home" will be replaced by "Show in Home" in the More menu.
Cheers!
The idea is that you want to keep these Google Docs but you don't want them showing up in Home. The goal is somewhat similar to the "Archive" feature in Gmail. This is for those who like a clean Docs List.
For students, a new year means new classes which hopefully means new Google Docs! But first let's clean the existing Docs List. Let's take this from the student's standpoint.
The Cleaning
First off, we need to create little bins for last semester's Docs. If you haven't already, I suggest creating a collection for each class. Click on the Create button and choose Collection.
 |
| Creating a New Collection |
 |
| Naming a Collection |
 |
| Applying Docs to a Collection |
Navigate to the Collection and click on the check box to show a blue check.
 |
| Choosing the Collection |
Repeat these steps until you are happy with your Docs and Collections. Cleaning done!
The "Archive"
The final step is to keep the Docs from showing up when Home is selected from the left pane. This is the default selection when the Docs List is opened.
Select the Collection you would like to "Archive" from the left menu. Select all Docs in that Collection by choosing the uppermost check box. Choose More and Don't show in Home.
Now these Docs have been "Archived." They will no longer show up in Home even if they are edited by you or other collaborators. This is different from Gmail, in that if someone responds to an archived message it will reappear in your Inbox.
This method can easily be undone by completing the last step again. "Don't show in Home" will be replaced by "Show in Home" in the More menu.
Cheers!
Thursday, December 29, 2011
An update to vlookup and arrayformula
For those trying to use arrayformula in Google Spreadsheets, I thought I would give a few examples that might help when designing your function. In addition, I've provided a general example where you can fill in your corresponding ranges to build the function. Let me know if you have any problems!
I've also embedded a spreadsheet here that shows a few examples of how to use this:
Also, if you want to play with this spreadsheet feel free to make your own copy here:
Using arrayformula with vlookup template
Cheers!
To customize use this key.
=arrayformula(if(row([range1])
where
[range1]: range of values to be looked up
[range2]: table of data where look up values are in column 2
N: number of column that has the data you want to returnI've also embedded a spreadsheet here that shows a few examples of how to use this:
Also, if you want to play with this spreadsheet feel free to make your own copy here:
Using arrayformula with vlookup template
Cheers!
Subscribe to:
Posts (Atom)