Using Google Docs as a data mashup platform

Guest post: Tony Hirst is a lecturer in the Department of Communication and Systems at the UK's Open University, co-founder of document discussion platform WriteToReply.org and member of the JISC's DevCSI Developer Focus Group. An aspiring "mashup artist", he blogs regularly at OUseful.info.

For several years, I have been exploring various ways of using online applications to grab and display data from around the web and represent it in a visual form. One fertile source of near-live data, particularly for sports results, is Wikipedia; but how can you get data out of Wikipedia and then display it in a chart, or on a map?


For the 2008 Olympics, I looked at how to create a map-based view of the overall medal tables using Google spreadsheets. With the Olympics coming round again - this time the 2010 Winter Olympics - I thought I'd take the opportunity to update that original mashup with a few tricks I've learned since then. In part as a teaching example, I came up with a recipe that illustrates a lot of functionality many people are unaware of, in a self-contained and hopefully coherent way - how to import data into a spreadsheet, how to write an application script, and how to use a spreadsheet as a database. The aim is to create a heat map of the current state of the medals table for the 2010 Winter Olympics that I can add to iGoogle.

The recipe runs as follows:

- Take one Winter Olympics Medal table on Wikipedia
- Use the importHTML function to import the table into a Google spreadsheet
- Filter out the name of each country from the imported table using either a Google Apps script function containing a regular expression or a SPLIT() formula; return the country name to the medal table spreadsheet
- Take one ISO country code table, found via a web search, and copy and paste it into a second spreadsheet worksheet. You will use this sheet as a database
- Using a =QUERY() formula applied to the ISO country code sheet, find the ISO country code for each country in the medal table. (Note that some extraneous space characters in the SPLIT country name require the trailing space to be recognized)
- Arrange the columns, by copying cells if necessary, so that you have a column of ISO country codes followed by number of medals. For example, ISO country code, number of gold medals, ISO country code, number of silver medals, and so on.
- Highlight a country code column and a medal tally column that are side by side, select a heatmap widget from the tools menu and configure it as required
- Embed your Winter Olympics 2010 Live Medals Table Heatmap in your blog or iGoogle from the Gadget menu.


As the Wikipedia medals table is updated, your medals table heatmap should be too. To preview the spreadsheet, please visit here.

A complete recipe is given in the OUseful.info blog post "Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets."

Posted by: Tony Hirst, Dept of Communication and Systems, The Open University, UK & OUseful.info

Read More

Google Docs: the tool for the 21st century classroom

Guest post: Tara Seale teaches 9th grade English in the Bryant Public School District and recently attended the Google Teacher Academy.

English teachers polled in the last decade of the last century about the one tool that they could not live without in their classrooms would probably select the overhead projector. In the first decade of the 21st Century, English teachers would probably choose a document camera, but in future decades, the tool will be web-based. I already teach in a web-based environment, and Google Docs is the web-based tool that has become the organizational center of my classroom.

I share assignments with my students as a view only file. Students make a copy of the file so they can annotate the directions. This is a weblink of an assignment: Expository Essay defining the word perseverance. This is an annotated copy of the assignment: Google Doc Annotated Copy of Expository Essay Assignment. No longer do I hear, "Mrs. Seale, can I have another copy of that assignment? I lost mine."

Docs also teaches organizational skills. Students create folders to keep up with assignments. The most important folder is the folder students share with the teacher. All graded writing goes into this folder, and it serves as a writing portfolio for the semester. I do not have to hunt student work; it is organized in a student folder. At the end of the day, I leave with just a laptop, no papers to lug around.

Each student folder is in a group class folder. The class folder contains each student's writing for the semester:

Each student's online writing portfolio folder is also shared with his or her parents. Parents can even comment on student work and participate in the revision and editing process.

For students, Google Docs is an invaluable tool in the writing process. Students do not need a flash drive to carry drafts to and from home. Also, students can share writing with peer editors. After peer editing, students move their final draft into their English 9 folder. As the final editor, I leave comments to assist the student in revising his or her final draft. It is satisfying to browse through the revision history and see that a student is considering each comment as they revise: Yea! They are really reading what I wrote! Usually, students do not read teacher comments that are hand written on paper, but it seems to work in Google Docs.

Recently, a student working at home asked if I could read her paper before she submitted it later that week. I left comments and asked questions on the Google doc as the student wrote and revised, and it turned into a successful tutoring session. Thanks Google Docs!

Posted by: Tara Seale, English Teacher and Instructional Technology Specialist

Editor's note: Google recommends you use Google Docs within the Google Apps Education Edition suite with your students.

Read More

A web clipboard for Google Docs

We want copying and pasting content within Google Docs to just work. So, today we’re launching a new web clipboard that improves copy and paste in Google Docs. This new clipboard temporarily stores items you’ve copied in the cloud, then allows you to paste them with proper formatting into other Google Docs.

Say you work for a cheese company and you've created a table in a Google spreadsheet summarizing cheese sales. You want to present the results to your coworkers using a Google presentation.


Now, you can just select your cells in the spreadsheet and copy them using the new clipboard menu.


In your presentation, select the cells from the web clipboard menu to paste.


Your spreadsheets table will now be in your presentation. It works just as you would expect.


The new web clipboard lets you copy content between documents, spreadsheets and presentations more easily and with improved fidelity, and this is just our first step. Note that while items in your web clipboard are available across browsers and across sessions, they do expire after a month.

We hope you find these changes useful, but please let us know how you think we can improve interaction between Google Docs on our forum.

Posted by: Ben Margolin, Engineer, Google Docs

Read More

Simple wedding planning with Google Docs

Cross posted on the Official Google Blog

We all want important life moments — like graduating from school, getting married or having your first baby — to be perfect. For many couples, your wedding is a chance to celebrate with everyone you care about; it's also the largest, most complicated party you'll ever host. From tracking guest RSVPs, to picking the right florist, DJ and caterer, to coordinating every last detail with your wedding party, it's no surprise that the process can become overwhelming and expensive.

After proposing to the woman of my dreams with 100 red roses, six months ago I started planning my own wedding. My fiancee and I decided to use Google Docs to manage every aspect of our wedding, starting with shared budget, guest list, to-do list and venue-tracking spreadsheets and keeping all our docs in our "Wedding" shared folder. I ended up talking to other couples who planned their weddings using Google Docs and discovered I wasn't alone in thinking that it helped save time and avoid headaches.

Today, I'm happy to share this knowledge in the form of over 20 wedding templates available in the Google Docs template gallery. These tools make it easy to estimate and track your wedding budget, collect addresses for invitations, compare vendors and much more. For example, take a look at the address book template below. Instead of emailing hundreds of guests and copy/pasting hundreds of addresses into a spreadsheet, you can send a Google form and collect addresses in a spreadsheet automatically:


Because these documents, spreadsheets and forms live online in the cloud, you can easily get help by sharing them with your parents or bridal party, and you can access them from the bakery, bridal shop or anywhere around town using your smartphone. Plus, you never have to worry about versions and email attachments, because everything is always up to date.

Having the tools to plan a wedding is a good start, but you also need to know what questions to ask when interviewing vendors and which factors to consider when inviting guests or choosing music. To give you a leg up, we've teamed up with StyleMePretty, a popular wedding blog, to add tips from wedding experts to each template. Style Me Pretty is also hosting a sweepstakes and asking engaged couples to share their wedding planning experiences: one randomly selected winner will receive free consultation with celebrity event planner Michelle Rago and a $500 gift certificate to Wedding Paper Divas.

We're excited to give more engaged couples tools to make the wedding planning process easier and more fun. To learn more about simplifying wedding planning with Google Docs and Style Me Pretty, check out docs.google.com/wedding.

Posted by: Peter Harbison, Product Marketing Manager

Read More

Save safely with new saving buttons

Many of you have told us you'd like consistent and intuitive features across all the Google Docs editors, so today, we’re happy to launch a couple of improvements to the way saving works in all of Google Docs.

Now it's much easier to tell, at a glance, when documents need to be saved, when documents are busy saving, and when documents have been saved into the cloud:
A clickable save button means your document has edits which haven't been saved yet and the timestamp lets you know when it was last saved. You can either wait for autosave to kick in after a few seconds, or you can manually save at any time using the keyboard (ctrl S on PC, cmd S on Macs), save button, or by going to through the file menu.

A disabled button reading “Saving” means your document is currently being saved, and should complete in a second or two.
A disabled button reading “Saved” means all data in this document is now saved, and the timestamp tells you when the last change happened. You can safely exit the application or continue editing the document.

These changes are available today in presentations and documents and will be coming to spreadsheets soon. We'll continue to improve consistency in Google Docs, so if you have suggestions about what we should tackle next, let us know on our forum and ideas page.

Posted by: Garry Boyer, Software Engineer and Joseph Wain, User Experience Designer

Read More

Free e-commerce catalogs managed with Google Docs

Guest post: Dan Salcedo is founder of the DC based international development organization OpenEntry, with the mission of helping small and medium enterprises (SMEs) worldwide benefit from the exciting new opportunities opened up by global e-commerce. Working with a team of volunteer developers, he built a catalog generator that enables SMEs in developing countries to create their own free e-commerce catalogs, managed via a Google spreadsheet.

Years ago, I noticed artisans in developing countries were selling their items to a long chain of middlemen that only paid them 10-15% of the final retail price, even through conventional fair trade channels. So I launched a non-profit organization to help artisans disintermediate all the middlemen by selling directly through e-commerce catalogs that they could create and manage themselves. We recently relaunched our OpenEntry catalog generator using a bunch of Google tools including Docs, Sites, Checkout, Picasa Web Albums, AdSense, and Apps Engine. And our User Manual, built on Google Sites, is full of YouTube videos. This enables us to offer totally free e-commerce catalogs (software, hosting, user support) to artisans and SMEs worldwide including the following:

www.NatureNepalHS.com

Nature Nepal-Herbal Care

Nepal

main.openentry.com/mereville

MerevilleyTrust

India

www.pupbd.org

Pollee Unnyon Prokolpo

Bangladesh

Each catalog is managed by filling out a Google spreadsheet with three sheets containing information on the company, products, and additional pages (see products sheet below).


The images are stored on Picasa Web Albums and sellers use Google Checkout (as well as PayPal and 2Checkout) to accept credit card payments. Google Sites helps users generate attractive HTML that can be added to some of the spreadsheet fields to improve aesthetics. Google App Engine reads the spreadsheet, then generates the catalog hosted on Google servers. These tools also made it surprisingly easy to enable OpenEntry catalogs to be managed with a smart phone.

Google recently added a feature enabling the ownership of their spreadsheets to be transferred to a third party, which makes it easy to transfer spreadsheets to the final SME vendor. This is very good news because it enables the OpenEntry User Support team in Nepal to set up catalogs and transfer ownership to the SME vendor who can then operate it securely. This also means we can now transfer blocks of catalog accounts to young, ambitious entrepreneurs anywhere in the developing world, enabling them to start their own legitimate enterprises. Even though the OpenEntry catalogs are free, the entrepreneurs can charge for setup, digital images, custom templates, training, maintenance, etc. And they can even do it from an Internet cafe until they can afford their own computer. This reinforces the conclusion of the United Nations Development Program evaluation of our platform (under its previous name, CatGen) that generated 4000 jobs for artisan women and "a relatively inexperienced group of young IT professionals" in Nepal.

Because this is a non-profit initiative, OpenEntry is seeking volunteers to help with a variety of technical tasks starting with template design.

Posted by: Dan Salcedo, Founder and CEO of www.OpenEntry.com, dsalcedo@OpenEntry.com

Read More