If you frequent the Docs forum or Drive forum you might have noticed a few new terms for describing Google Docs. With the introduction of Drive it has been difficult or many users to figure out what exactly is going on and what exactly everybody is talking about. You might have also noticed that "Documents" in your upper black bar now says "Drive." I hope my guide below can help with the rebranding!
Google Drive (or Drive)- This is the all encompassing term for cloud storage of files using Google's service. You can use this term by saying things like "Hey, my computer died, but luckily I have everything saved in my Drive." Drive also has various apps associated with it that allow you to collaborate in real-time. These files are in a Google format and must be edited in a browser. Currently, only Docs (see below) allow for offline editing and offline editing is not supported on mobile.
Doc(s) - Formerly known as Documents. This will be the most confusing term going forward as the term "Google Docs" use to encompass Documents, Spreadsheets, Presentations, and Drawings. This now refers to the Google format for word processing. Google Documents is now Google Docs. A single Google Docs file is called a Google Doc. You can use this term by saying something like "Hey, I'm sick of sending this Word file through email. How about turning it into a Google Doc so we can collaborate in real time?" (In my mind, this is much better and how it should have been from the beginning.)
Sheet(s) - Formerly known as Spreadsheets. A single Google Sheets file is called a Google Sheet. Not to be confused with a tab in the Sheet. You can use this term by saying something like "Hey, I want to use Google Sheets to plan our next vacation budget!"
Other notes: There has traditionally been dissimilarities between Microsoft Excel and Google Sheets terms. We will see how this goes but in the past the term "workbook" has generally been avoided. In the Google Sheets world, the terms "tab" and "sheet" will be synonymous and while the term "Sheets" could be used in place of "workbook." However, I am going to strongly advise that "workbook" be used to describe the collection of "tabs" or "sheets" in a single file (regardless of whether we are talking about Excel or Sheets). The capitalization of Sheets will indicate the Drive app only and a lower-case "sheets" will refer to a "tab." A lower-cased "spreadsheet" is also synonymous with "sheets" and "tabs" but the upper-case "Spreadsheets" no longer exists and should not be used. You might want to read that a few times.
Slide(s) - Formerly known as Presentations. This one is an easy transition and can be used by saying something like "Hey, you know building this presentation would be much easier if we did this with Slides?"
Drawings - Drawings is still Drawings.
As you can see this will be a difficult transition but I hope we can all use the same terms when discussing Google Drive on the forums or anywhere else.
Please comment if you think anything is unclear and I can make some edits!
Note: These are just my conclusions from using Google Drive but I hope Google releases something soon explaining everything. But for now...use this.
Cheers!
Monday, October 22, 2012
Saturday, September 22, 2012
Person Dispatching and the Rectified 5-cell Simplex Pentagon
There are a bunch of wonderful apps and location-based services for managing supply lines, keeping track of door-to-door sales associates, and optimizing these paths. But I came up with a quick model using Google Spreadsheets.
The basic idea is that you have a map with n locations that you have to visit. The goal is to find out how many places you could go in a day based on a bunch of parameters (distance between points, average speed, etc.). So how do you do it?
The first thing I did was determine what my map would look like. In the real world you would actually know the distances between each location and how many locations you have. In this case I don't, so I guessed using a mathematically-pleasing design. I decided on a orthographic projection of a rectified 5-cell pentagon, which is a complicated way of saying, in 2 dimensions, a small pentagon inside of a big pentagon.
To do the calculations you can determine distances from each points as long as you know the length of each side (T) and the length of diagonals (D) of the pentagons. You can adjust this in I2 and I3 on each of the person tabs. Other variables you can change include average speed while traveling, amount of time spent at each location, some lag time to account for variation, and number of hours worked per day.
Once you have added all of your information you can get a forecasted number of locations to be visited per day as well as other random information. I've also included a sensitivity analysis by varying time at location and lag time, all as percentage change from current forecast.
If you'd like to play around with if you can access the spreadsheet here and go to File>Make a copy for an editable version.
Enjoy!
The basic idea is that you have a map with n locations that you have to visit. The goal is to find out how many places you could go in a day based on a bunch of parameters (distance between points, average speed, etc.). So how do you do it?
The first thing I did was determine what my map would look like. In the real world you would actually know the distances between each location and how many locations you have. In this case I don't, so I guessed using a mathematically-pleasing design. I decided on a orthographic projection of a rectified 5-cell pentagon, which is a complicated way of saying, in 2 dimensions, a small pentagon inside of a big pentagon.
To do the calculations you can determine distances from each points as long as you know the length of each side (T) and the length of diagonals (D) of the pentagons. You can adjust this in I2 and I3 on each of the person tabs. Other variables you can change include average speed while traveling, amount of time spent at each location, some lag time to account for variation, and number of hours worked per day.
Once you have added all of your information you can get a forecasted number of locations to be visited per day as well as other random information. I've also included a sensitivity analysis by varying time at location and lag time, all as percentage change from current forecast.
If you'd like to play around with if you can access the spreadsheet here and go to File>Make a copy for an editable version.
Enjoy!
Tuesday, August 7, 2012
Manipulating Form Data
Hi all,
Sorry its been a while since I've posted. To make up for it I'm doing a Hangout On Air tomorrow about manipulating Form data. I'll post the video here afterwards. Enjoy!
Ted J
Sorry its been a while since I've posted. To make up for it I'm doing a Hangout On Air tomorrow about manipulating Form data. I'll post the video here afterwards. Enjoy!
Ted J
Wednesday, May 30, 2012
=sumproduct() follow-up with live stream!
Thanks to those who watched live. You should be able to see the recording below. Enjoy!
The above video is related to the shared spreadsheet below:
Vertical Summing Examples
Functions covered include =sumproduct(), =sumif(), and =query().
Cheers!
The above video is related to the shared spreadsheet below:
Vertical Summing Examples
Functions covered include =sumproduct(), =sumif(), and =query().
Cheers!
Saturday, May 19, 2012
Using =sumproduct() in Google Spreadsheets
It has been a while since I posted but I promise to have some exciting posts soon! But for today I thought I would show a quick example of a way to flip sumif behavior.
Consider a situation where all odd number rows have names and all even number rows have a numerical value in a single column. In my example, I either have Ted or Adam in the odd number rows. The goal here is to find the sum of the values directly below each time Ted appears and to find the sum of the values directly below each time Adam appears.
Formula in D1: =SUMPRODUCT(N(A$1:A$19=C1),A$2:A$20)+INDEX(0,1)
Formula in D2: =ARRAYFORMULA(SUMPRODUCT(N(A$1:A$19=C2),A$2:A$20))
What does sumproduct() do?
Sum product takes up to ??? (documentation says 30 but I think it is closer to 255) ranges (in Google Spreadsheets) or 255 ranges (in Excel) and multiplies the corresponding values together and then sums up all of the values. If my function was =sumproduct({2,3,4},{1,2,3}) the result would be 20 (2*1 + 3*2 + 4*3). In order for the sum and multiplication to work we have to turn our criteria into a number. Therefore we use the N() function to turn a value into 1 if the criteria is met or 0 if not. The second step is to define the range we want to multiply the first range by and that is basically the same range we had before but shifted down a cell. Instead of A1:A19 it becomes A2:A20.
To visualize what is going on see the tab called Visualize in the example or watch the video below.
Now that we have defined our ranges to be multiplied, the function is ready to go! Since SUMPRODUCT must be array enabled to work we must apply an array-enabler. I've illustrated two ways to do this depending on whether you are using Google Spreadsheets, Excel, or both. By wrapping the whole function with arrayformula() you enable the function to do array calculation. You can also do this by adding index(0,1) which when evaluated is always zero and has no effect on the function, but enables the array calculation for Google Spreadsheets and Excel. Arrayformula() is not supported by Excel and you will see a #NAME? error.
Enjoy and feel free to add any questions in the comments.
Also, see the video explanation recorded as a Google Hangout On Air and feel free to make a copy of the Spreadsheet to explore.
Cheers!
Consider a situation where all odd number rows have names and all even number rows have a numerical value in a single column. In my example, I either have Ted or Adam in the odd number rows. The goal here is to find the sum of the values directly below each time Ted appears and to find the sum of the values directly below each time Adam appears.
Formula in D1: =SUMPRODUCT(N(A$1:A$19=C1),A$2:A$20)+INDEX(0,1)
Formula in D2: =ARRAYFORMULA(SUMPRODUCT(N(A$1:A$19=C2),A$2:A$20))
What does sumproduct() do?
Sum product takes up to ??? (documentation says 30 but I think it is closer to 255) ranges (in Google Spreadsheets) or 255 ranges (in Excel) and multiplies the corresponding values together and then sums up all of the values. If my function was =sumproduct({2,3,4},{1,2,3}) the result would be 20 (2*1 + 3*2 + 4*3). In order for the sum and multiplication to work we have to turn our criteria into a number. Therefore we use the N() function to turn a value into 1 if the criteria is met or 0 if not. The second step is to define the range we want to multiply the first range by and that is basically the same range we had before but shifted down a cell. Instead of A1:A19 it becomes A2:A20.
To visualize what is going on see the tab called Visualize in the example or watch the video below.
Now that we have defined our ranges to be multiplied, the function is ready to go! Since SUMPRODUCT must be array enabled to work we must apply an array-enabler. I've illustrated two ways to do this depending on whether you are using Google Spreadsheets, Excel, or both. By wrapping the whole function with arrayformula() you enable the function to do array calculation. You can also do this by adding index(0,1) which when evaluated is always zero and has no effect on the function, but enables the array calculation for Google Spreadsheets and Excel. Arrayformula() is not supported by Excel and you will see a #NAME? error.
Enjoy and feel free to add any questions in the comments.
Also, see the video explanation recorded as a Google Hangout On Air and feel free to make a copy of the Spreadsheet to explore.
Cheers!
Saturday, March 24, 2012
Barack Obama, Alex Morgan, Ted Juch and March Madness
Well, we know who the Elite 8 are for the NCAA Men's College Basketball Tournament. How is your bracket doing? I'm sitting somewhere between Alex Morgan and Barack Obama.
I've seemed to iron out most of the kinks for using Gdocs to run your blog so I'm posting a small pool that I created below. There have been a few more feature requests but unfortunately I haven't gotten around to doing them. This just leaves room for more improvements for next year!
Enjoy!
I've seemed to iron out most of the kinks for using Gdocs to run your blog so I'm posting a small pool that I created below. There have been a few more feature requests but unfortunately I haven't gotten around to doing them. This just leaves room for more improvements for next year!
Enjoy!
Thursday, March 15, 2012
Using TedTournament() vs GoogleTournament()
Google updated their function yesterday and released a template today. I had nothing to do with the template that was released today but it was a copy of last year's with the year replaced as 2012. This proof is in 'Full Bracket'!C7 and any of the play-in games if you are curious. So if you want the updates you'll have to use my versions.
That being said, my templates should be functioning correctly. I've modified the back end so that Google's shortnames match up with Adam Bartlett's guesses at the shortnames. Therefore, assuming everything flows correctly on Google's end, I won't have to manually update my master spreadsheet. It will be updated as soon as GoogleTournament() gets updated.
Since this is a Docs blog I'll explain how I automated the back end. My master sheet before had empty cells where I would update the teams as they won. This means that if I am not near a computer or internet, you would have to wait and this really wasn't acceptable to me. Now, my mastersheet combines a vlookup, googletournament, and iferror. I created a table, which I will call "Helper" that lists Google's shortnames in column A and column B has Adam's names.
It looks something like =iferror(vlookup(googletournament(year,"men",round,slot,"statistic,team),Helper,2,FALSE))
This finds the shortname of the team using Google Tournament, matches it up with Adam's version of the shortname and returns Adam's version (which matches our template). If vlookup fails then it returns blank. The vlookup only fails with googletournament fails which indicates a game hasn't been played yet.
Anyways, I think my template is more robust that Google's template and easier to use, but the bugs are still being ironed out. Unfortunately, I can only update my template for any bugs that are found and not your own copies of my template. I'm trying to make any updates easily translatable.
Update:
There was an error in the point calculations, which I should have noticed from the beginning. There is an extra row in the full template vs the blank template. This means that my method of assigning points wasn't matching correctly. I've updated the Points sheet to calculate correctly. You can either make a new copy of my template or copy and paste D5:F67 from the points tab on my template to yours. You will have to unhide D:E and then rehide after you make the adjustments. The Single Bracket has also been updated. Replace G4 on the points tab with:
=if(F4="TBD",iferror(1/0),indirect("'"&G$1&"'!"&$E4&$D4-1)) <--Drag this all the way down to G66
and I4 with
=arrayformula(if(len(F4:F)>0,if(G4:G=F4:F,C4:C,0),0)) <--You don't have to drag this.
Next week I'll update the Google Sites template to show how all of this could be displayed through Google Sites. I'm planning on using 5 brackets in the pool along with Obama's. Should be fun to see how people match up!
Happy Madness!
The follow links are the same links from my original post:
That being said, my templates should be functioning correctly. I've modified the back end so that Google's shortnames match up with Adam Bartlett's guesses at the shortnames. Therefore, assuming everything flows correctly on Google's end, I won't have to manually update my master spreadsheet. It will be updated as soon as GoogleTournament() gets updated.
Since this is a Docs blog I'll explain how I automated the back end. My master sheet before had empty cells where I would update the teams as they won. This means that if I am not near a computer or internet, you would have to wait and this really wasn't acceptable to me. Now, my mastersheet combines a vlookup, googletournament, and iferror. I created a table, which I will call "Helper" that lists Google's shortnames in column A and column B has Adam's names.
It looks something like =iferror(vlookup(googletournament(year,"men",round,slot,"statistic,team),Helper,2,FALSE))
This finds the shortname of the team using Google Tournament, matches it up with Adam's version of the shortname and returns Adam's version (which matches our template). If vlookup fails then it returns blank. The vlookup only fails with googletournament fails which indicates a game hasn't been played yet.
Anyways, I think my template is more robust that Google's template and easier to use, but the bugs are still being ironed out. Unfortunately, I can only update my template for any bugs that are found and not your own copies of my template. I'm trying to make any updates easily translatable.
Update:
There was an error in the point calculations, which I should have noticed from the beginning. There is an extra row in the full template vs the blank template. This means that my method of assigning points wasn't matching correctly. I've updated the Points sheet to calculate correctly. You can either make a new copy of my template or copy and paste D5:F67 from the points tab on my template to yours. You will have to unhide D:E and then rehide after you make the adjustments. The Single Bracket has also been updated. Replace G4 on the points tab with:
=if(F4="TBD",iferror(1/0),indirect("'"&G$1&"'!"&$E4&$D4-1)) <--Drag this all the way down to G66
and I4 with
=arrayformula(if(len(F4:F)>0,if(G4:G=F4:F,C4:C,0),0)) <--You don't have to drag this.
Next week I'll update the Google Sites template to show how all of this could be displayed through Google Sites. I'm planning on using 5 brackets in the pool along with Obama's. Should be fun to see how people match up!
Happy Madness!
The follow links are the same links from my original post:
Wednesday, March 14, 2012
Update on March Madness Pool Templates (How I fixed it)
It seems that the =googletournament() function will most likely not be updated in time. For those still wishing to use Google Docs for your own bracket or Pool, however, you still have an option. I've updated the templates I created to still work. Instead of getting your information from Googleland you will be getting it from me. As long as you don't change any grey background fields on the spreadsheets, your brackets will update with the winners automatically, assuming I am near a computer at the completion of a game (I probably will be as I'm a huge basketball fan).
Since this blog is about Google Docs, I thought I might quickly step into that I did on the backend of these templates to make it work.
I decided that my method would be to create a master spreadsheet that I update. My template then uses =importrange() to pull various ranges from the master spreadsheet. As long as you have viewing rights to a spreadsheet, you can pull data from it using importrange(). So this should work.
I divided the Full Bracket tab for both the Single and Group Template into 3 sections; the left side of the bracket, the right side of the bracket and the middle. This way I only had to write 3 importrange() functions. I decided against using one. Now, the Full Bracket tab is pulling in the data from my master spreadsheet in three sections. As soon as I update the master sheet, anybody who references my master sheet (anybody using my templates) will see their data update within a few minutes. The blank bracket templates simply reference the Full Bracket so that we don't end up with a million importrange() function (you can only actually have up to 50).
If anything, the spreadsheets might actually be quicker because 3 importrange() functions should be lighter than a billion =googletournament() functions.
Thanks for being patient. Hopefully next year Google will be able to fix the function so that it works (I'll make sure of it this time).
Cheers and good luck with your brackets!
Since this blog is about Google Docs, I thought I might quickly step into that I did on the backend of these templates to make it work.
I decided that my method would be to create a master spreadsheet that I update. My template then uses =importrange() to pull various ranges from the master spreadsheet. As long as you have viewing rights to a spreadsheet, you can pull data from it using importrange(). So this should work.
I divided the Full Bracket tab for both the Single and Group Template into 3 sections; the left side of the bracket, the right side of the bracket and the middle. This way I only had to write 3 importrange() functions. I decided against using one. Now, the Full Bracket tab is pulling in the data from my master spreadsheet in three sections. As soon as I update the master sheet, anybody who references my master sheet (anybody using my templates) will see their data update within a few minutes. The blank bracket templates simply reference the Full Bracket so that we don't end up with a million importrange() function (you can only actually have up to 50).
If anything, the spreadsheets might actually be quicker because 3 importrange() functions should be lighter than a billion =googletournament() functions.
Thanks for being patient. Hopefully next year Google will be able to fix the function so that it works (I'll make sure of it this time).
Cheers and good luck with your brackets!
Sunday, March 11, 2012
March Madness Bracket/Pool Templates
The long awaited Bracket has arrived! See below for links to the template. You will have to make your own copies. Note: The templates will not work until the field is set and Google adds the information to the =googletournament() function.
Individual Bracket
The individual bracket can be used for you to keep track of your own bracket, or serve as a template to be used in a group bracket pool. The group will be discussed below.
Full Bracket:
The Full Bracket tab shows what the tournament currently looks like. You don't make any selections here but this shows how powerful =googletournament() is. The only editable cells are for choosing men or women and then the year. This bracket was built with the men's tournament in mind and has not been tested for the women's tournament.
Points:
This tab shows you the current points you have won for your picks. You can choose how many points are given each round. I recommend using an exponential system as it gets more difficult to predict games that happen later in the tournament. The exponential system is the default. This tab is also where you apply the tiebreaker. This number will show up after the final results of the final game. The tie breaker asks each person to guess at the final score of the championship game. The actual summed scores for each time in the final game is subtracted from the guesses of the final scores for each time in the final game. This creates a differential between the actual results and the guessed results. In the event of a tie, whoever has the smaller differential wins. The absolute value is taken so there is not a penalty for going over.
Your Bracket:
This is where you make your choices. Only edit the yellow cells and make sure to choose what you think the final score will be. The game cells are validated so you can only choose the possible teams. Make sure you put your name in the name box and email address if you are part of a group. This will help the pool admin with sending you information. Note: You will not be able to fully fill out the bracket until the field is completely set.
Helper:
This sheet is hidden but builds the validation for each team choice. You can click around to see what it does. Basically, it looks at the choices you made in the previous round and only allows you to choose one of the possible two teams. This is an upgrade from the past in that you could type pretty much anything in the cells.
HELP:
The HELP sheet gives a few helper instructions.
Group Bracket
The group bracket creates a place for many brackets to be analyzed for scoring and competition. The current bracket has room for 50 brackets. I'm not sure how many brackets the template can take before becoming slow. However, if you run into this issue, I would advise making "Groups" and aggregating the Standings tab in a separate Standings spreadsheet to keep track of who is winning. I'll comment on doing that below.
Full Bracket:
This tab is the exact same as in the single bracket template.
Bracket Participants:
Use this sheet to indicate who is participating in your pool. You will only need to update the Name column. Make sure to include First and Last name or some sort of identifying to make sure that everyone in the pool has unique usernames. This is important for scoring purposes. The name that you put here must match the name of the tab that corresponds with the person. For example, if Big Bird was in our pool we must type Big Bird on the Bracket Participants tab and the bracket corresponding to Big Bird must say exactly Big Bird's Bracket.
Standings:
Standings sorts all of the pool participants by score and then by differential. As a reminder, the absolute value of the differential is taken so there is no penalty for going over. You don't need to touch anything on this tab.
Points:
If there is a tab that is going to bog things down, it will be this tab. This tab scores the brackets by grabbing all of the important information from each bracket and scoring it against the actual results of the tournament by assigning points for correct guesses. If you would like to change the point values for each round you can do it here in column C. You should only update the cells with a yellow background. This sheet uses the indirect() function to search through the Spreadsheet for a bracket that matches a name from the Bracket Participants tab. This is why the tab name of the individual must match exactly the name in the participants. If this tab starts bogging down the entire spreadsheet, see below for some tips.
HELP:
This tab provides simple help information about the group version of the spreadsheet.
Blank Bracket:
Here is a blank bracket for using one method of setting up the pool. I will now discuss two methods below.
Setting up your pool Method 1
The first method for setting up your pool requires you to make multiple copies of the blank bracket tab. You will need a copy for each participant in your pool. These tabs much be renamed for the participant and match the name exactly in this format "[Name]'s Bracket". I suggest using unique identifiers or first name and last name. Once you have this set up for each person in your pool, you will need to share this spreadsheet with them. You can either share the entire spreadsheet and hope that users don't edit other's brackets or use tab level permissions (click on the down arrow for the tab and go to Protection to choose who can edit the tab) and allow only the user whose name is on the tab to edit the tab. I would suggest this method for those who have a few people in the pool (<10 or so). Once everyone has set up their bracket you can change the editing rights to view only for all collaborators so that they cannot change their results.
Update: You will not be able to use the Helper for validation on the Blank Bracket. If you use this method your participants must type the short name for the team you choose to win. Therefore, as a bracket admin someone must make sure each choice is a legitimate choice. This is a disadvantage to Method 1. Thanks to Hells_ for pointing this out!
Setting up your pool Method 2
If you have a lot of people in your pool you might consider having multiple Group brackets and requiring your pool participants to fill out their own single bracket and share it with you. The steps for achieving this are as follows:
Individual Bracket
The individual bracket can be used for you to keep track of your own bracket, or serve as a template to be used in a group bracket pool. The group will be discussed below.
Full Bracket:
The Full Bracket tab shows what the tournament currently looks like. You don't make any selections here but this shows how powerful =googletournament() is. The only editable cells are for choosing men or women and then the year. This bracket was built with the men's tournament in mind and has not been tested for the women's tournament.
Points:
This tab shows you the current points you have won for your picks. You can choose how many points are given each round. I recommend using an exponential system as it gets more difficult to predict games that happen later in the tournament. The exponential system is the default. This tab is also where you apply the tiebreaker. This number will show up after the final results of the final game. The tie breaker asks each person to guess at the final score of the championship game. The actual summed scores for each time in the final game is subtracted from the guesses of the final scores for each time in the final game. This creates a differential between the actual results and the guessed results. In the event of a tie, whoever has the smaller differential wins. The absolute value is taken so there is not a penalty for going over.
Your Bracket:
This is where you make your choices. Only edit the yellow cells and make sure to choose what you think the final score will be. The game cells are validated so you can only choose the possible teams. Make sure you put your name in the name box and email address if you are part of a group. This will help the pool admin with sending you information. Note: You will not be able to fully fill out the bracket until the field is completely set.
Helper:
This sheet is hidden but builds the validation for each team choice. You can click around to see what it does. Basically, it looks at the choices you made in the previous round and only allows you to choose one of the possible two teams. This is an upgrade from the past in that you could type pretty much anything in the cells.
HELP:
The HELP sheet gives a few helper instructions.
Group Bracket
The group bracket creates a place for many brackets to be analyzed for scoring and competition. The current bracket has room for 50 brackets. I'm not sure how many brackets the template can take before becoming slow. However, if you run into this issue, I would advise making "Groups" and aggregating the Standings tab in a separate Standings spreadsheet to keep track of who is winning. I'll comment on doing that below.
Full Bracket:
This tab is the exact same as in the single bracket template.
Bracket Participants:
Use this sheet to indicate who is participating in your pool. You will only need to update the Name column. Make sure to include First and Last name or some sort of identifying to make sure that everyone in the pool has unique usernames. This is important for scoring purposes. The name that you put here must match the name of the tab that corresponds with the person. For example, if Big Bird was in our pool we must type Big Bird on the Bracket Participants tab and the bracket corresponding to Big Bird must say exactly Big Bird's Bracket.
Standings:
Standings sorts all of the pool participants by score and then by differential. As a reminder, the absolute value of the differential is taken so there is no penalty for going over. You don't need to touch anything on this tab.
Points:
If there is a tab that is going to bog things down, it will be this tab. This tab scores the brackets by grabbing all of the important information from each bracket and scoring it against the actual results of the tournament by assigning points for correct guesses. If you would like to change the point values for each round you can do it here in column C. You should only update the cells with a yellow background. This sheet uses the indirect() function to search through the Spreadsheet for a bracket that matches a name from the Bracket Participants tab. This is why the tab name of the individual must match exactly the name in the participants. If this tab starts bogging down the entire spreadsheet, see below for some tips.
HELP:
This tab provides simple help information about the group version of the spreadsheet.
Blank Bracket:
Here is a blank bracket for using one method of setting up the pool. I will now discuss two methods below.
Setting up your pool Method 1
The first method for setting up your pool requires you to make multiple copies of the blank bracket tab. You will need a copy for each participant in your pool. These tabs much be renamed for the participant and match the name exactly in this format "[Name]'s Bracket". I suggest using unique identifiers or first name and last name. Once you have this set up for each person in your pool, you will need to share this spreadsheet with them. You can either share the entire spreadsheet and hope that users don't edit other's brackets or use tab level permissions (click on the down arrow for the tab and go to Protection to choose who can edit the tab) and allow only the user whose name is on the tab to edit the tab. I would suggest this method for those who have a few people in the pool (<10 or so). Once everyone has set up their bracket you can change the editing rights to view only for all collaborators so that they cannot change their results.
Update: You will not be able to use the Helper for validation on the Blank Bracket. If you use this method your participants must type the short name for the team you choose to win. Therefore, as a bracket admin someone must make sure each choice is a legitimate choice. This is a disadvantage to Method 1. Thanks to Hells_ for pointing this out!
Setting up your pool Method 2
If you have a lot of people in your pool you might consider having multiple Group brackets and requiring your pool participants to fill out their own single bracket and share it with you. The steps for achieving this are as follows:
- Share the Single Bracket as view-only (or link them to the single bracket template above)
- Ask them to make a copy of this sheet and rename accordingly (they will have to be signed into a Google Account)
- Have them fill out the bracket and rename the "Your Bracket" tab with their name
- Once they have completed the bracket, they must share it with whoever is running the group pool
- Once shared, the pool admin must open the shared bracket, select the tab that shows their choices and choose "Copy to..."
- Select the group bracket as the target location of the copy.
- Open the group bracket and you will see a "Copy of [Name]'s bracket"
- Rename the tab as "[Name]'s Bracket" and add their name that matches their bracket name to the list of participants.
- OPTIONAL: Share the Group bracket with this person.
- You're Done! Now watch the tournament!
Tips
- If the spreadsheet becomes bogged down, create multiple group spreadsheets and assign people to a group alphabetically. For scoring, you can then create a separate spreadsheet that uses importrange() to grab the Standings tab from each group. You can use vmerge() from the scripts gallery to join the data or lock the ranges to grab only x amount of rows. Then you can use a filter() or query() on your data to find out who is winning. See a template below for doing that.
- If the spreadsheet becomes bogged down considering copying and pasting values only on the Points tab. Since the Points tab grabs the winning choices from the individual brackets you really only need to do this once. After the brackets are "locked" you can get rid of all of the indirect() functions by copying and pasting values only. Just know that any changes you make to an individual bracket will not be updated on the points. But this shouldn't matter because people shouldn't be making changes to brackets after the tournament has started.
Final Thoughts
This might seem somewhat complicated but I think it really shows the power of having a spreadsheets online, collaboration, custom functions, and integration with other Google products. I'm running a pool where I created a Google Site to hold all of the information using Method 2. I've included a link to an example site that will be created once the tournament gets going. Enjoy!
If you have any questions, feel free to comment below. I also might hold a Hangout on Wednesday night CST to entertain any questions or just talk about how this works. Add me to a circle on Google+ to learn more about that.
...and since I'm from St. Louis...GO BILLS!
Cheers!
Saturday, February 25, 2012
March Madness Is Coming!!!
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!
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!
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!
Subscribe to:
Posts (Atom)