Posts tagged Google Docs

Linking to a Cell in a Google Spreadsheet

1
6 months ago
by Seth Battis in

Because I can’t stop playing with something that already works fine, I’ve continued to tweak my syllabus-generating spreadsheet. With the addition of an external script, I’m able to link directly to a specific cell in the spreadsheet — letting me share a link to my syllabi that takes people not just to the overall document, but to the current day (or the next day that’s closest to the current day) in the syllabus. The script could be used to link to any cell in any published Google Spreadsheet, with the caveat that if there are multiple cells with that match that value, the link will take you to the first appearance of the value in the spreadsheet.

$url = $_REQUEST["url"];
$key = $_REQUEST["key"];
$anchor = $_REQUEST["anchor"];
 
$html = file_get_contents($url);
$output = str_replace($key, "<a name="$anchor"></a>$key", $html);
$output = str_replace("href='/", "href='https://docs.google.com/", $output);
 
header("Content-Type: text/html");
echo $output;

This takes a GET URL of the format http://server/script?url=[url]&key=[key]&anchor=[anchor]#[anchor], where…

  • The URL is the URL of the published Google spreadsheet location.
  • The key is the text to search for in the spreadsheet (so you could link to anything in the published HTML, but it’s easiest to link to a unique value in a cell).
  • The anchor is the name of the HREF anchor you will be creating (note that you then need to, well… link to that anchor).
Enjoy. You can try it out on my server:
URL:
Key:
Anchor:

Nota Bene: You have to add your own link to the anchor — this is a quick, slapped-together connection to my script.

Update: I was sitting here staring at the script, trying to figure out why I hadn’t put the anchor tag around the key, and then I realized what’s going on. The key (on my spreadsheet) is formatted to be white text on a white background (Rothko-style, if you will). If it gets converted to an anchor, its styling is affected and (without more work in the script) it turns blue and underlined. Lame. So… the anchor goes before the key, so the key’s CSS style won’t be affected.

This is why you document your code. Even when it’s short.

Better Ways to Share Documents (than Email Attachments)

2
6 months ago
by Seth Battis in

I had hoped that someone else would have put together a handy flyer to explain better options for sharing files than sending email attachments. But in a casual search of the web, none came up. This is my initial pass at the problem of presenting the Web 2.0 and the cloud to folks who are comfortable with email, but who don’t feel confident in their use of other technologies.

The first pass is truly aimed at providing a flyer that could be tacked up next to someone’s monitor.

I made a second version, which I posted to Google Docs, so that the links are clickable as well.

If you’d like to examine the assumptions and reasoning behind this, I have a flowchart that I felt… might not be as helpful in conveying these ideas.

For what it’s worth, when I was discussing the response to this broadsheet (broadside?) among the folks at school, my wife asked me if I sent this as an attachment.

She’s a funny one.

Building a Google Earth Tour in a Spreadsheet

1
2 years ago
by Seth Battis in , , ,

For the last couple of months, one of my high school classes at Jewish Day School has been working on building an interactive tool about the Six Day War for a middle school curriculum unit. They have put a lot of work into researching their data and laying it out in Google Earth, and now we’re putting together a tour of the data for the middle school students, that will also teach them a little about how to use Google Earth. It’s been enormously fun.

But. But, we now have to do some programming or some very careful recording in Google Earth to create this tour (a là Al Gore’s Climate Change tours). And my students are far more interested in the design end of things than in the coding end of things. They’re great at what they do, but niggling coding details give them head aches.

So I built a Google Docs spreadsheet, into which they can plug various pertinent information and out of which comes valid KML instructions that will define the tour. I’m a little proud of this — I don’t think that there’s anything else quite like it out there. There are three kinds of information that need to be entered:

  1. SoundCue — any audio that should be played in the background. You can either enter the whole URL to the MP3 file that should be played, or just the file name (and make sure that you enter the path in which all the files can be found on the KML Generator tab). You also need to tell us both when the cue should be played and how long it should last.
  2. AnimatedUpdate — anything that should be happening in Google Earth in terms of showing photo overlays or placemarks or polygons or what-have-you. Enter what should happen, and when (and, if relevant, for how long).
  3. FlyTo — any time we change where (or when) we’re looking at Google Earth. Enter when and how long the transition should take, and the date (and time) that we’re meant to be looking at.

The AnimatedUpdate and the FlyTo also expect the user to enter KML code — which can be copy-and-pasted from Google Earth KML exports for each transition. Happily, if something needs to happen more than once, entering the KML for the first instance will automatically populate future instances. In addition, the pauses necessary to synch up all of the action are calculated by the spreadsheet.

Update March 25, 2011: The spreadsheet above is our live work (since I kept updating the spreadsheet after this post).

The end result is both a visual timeline of the tour (helpful for debugging any weird errors) and also KML code that can be copy-and-pasted out of Google Docs and into a KML file. (Caveat emptor: depending on what you’re pasting the KML into — I like Xmplify — you may see that there is a leading and trailing quote that need to be manually deleted.)

Right now the spreadsheet can handle tours up to four minutes and 10 seconds in length (250 seconds, for those of you keeping score at home). This is because I was originally copying the KML out of the KML Orderer worksheet, and Google Docs supports pasting of up to 1000 rows in total. You’ll see that the current KML worksheet is a single cell, getting around this limitation, but I didn’t bother to extend any of the other worksheets. Just make sure you fill down all the formulas if you extend any of the sheets!

Here’s a link to a scratch copy of the spreadsheet. Feel free to copy and use it for your own purposes — let me know how you used it!

10 Things Better than Email Attachments

1
3 years ago
by Seth Battis in

One of my responsibilities at Jewish Day School is to write a weekly “tech tips” column for the online faculty news. This is one such tip (and it is, again, a bit FirstClass-centric, focused on some of our internal systems — we’re running a WPMU blog server and a MindTouch DekiWiki).

We won’t rehearse all of the problems with email attachments here (Can I open that file? What happened to my disk quota!? Which version was it?) Instead, let us focus on things that improve the experience. In fact, here’s a short video Top Ten list:

Links from the video

Google Docs Forms for Critique/Feedback

0
3 years ago
by Seth Battis in , , ,

This post is part of a series that are components of my “Expert Plan” at my school, looking to create a shared resource for my colleagues as the school moves towards greater adoption of laptops and technology in our pedagogy.

The Model

I wanted to structure the feedback that my students presented to each other during our video critique. We often brainstorm the criteria that we will be using to review work as it is presented, and I post our criteria on the board or on the wiki as a reminder throughout the process. This time, I created a new Form in Google Docs and entered our criteria as questions. I then embedded the form in our class notes for the day, and each student filled out the form as we viewed video. I embedded the responses as a spreadsheet on a linked page, so that the the students could review the feedback they had received and post their responses to our class blog.

In Practice

Creating the form live went relatively smoothly — the only hang-up was my inability to type in public. Fortunately, the students were proofreading on the screen and caught me when I made errors. They were also able to help guide me when I got distracted and forgot what I was doing (“Mr. Battis, we’ve already got that question at the bottom of the screen…”).

It took very little instruction for the students to figure out how to use the form. The most complicated thing they had to do was refresh the page after they had submitted their feedback so that they could get a fresh, blank form for the next video.

We settled into a routine where I played each video through twice, once for them to watch, and once to remind them of details as they entered their feedback.

Reflection

After we finished reviewing videos and posting feedback, I put the question to the students: was this better, worse or the same as having a verbal critique of the same material (which we had done in the earlier digital photography unit). The response, by and large, appeared to be that this was actually really helpful: there were enough things to pay attention to while watching the video that being able to take notes into the form let them not forget things that were important.

Google Docs Syllabi

2
3 years ago
by Seth Battis in , , , ,

This post is part of a series that are components of my “Expert Plan” at my school, looking to create a shared resource for my colleagues as the school moves towards greater adoption of laptops and technology in our pedagogy.

The Model

This started out as simply a way of addressing my own personal predilections: I’m tired of trying to build a syllabus on a calendar (the calendar doesn’t present the syllabus in a useful view), or in a spreadsheet (changing dates becomes a chore), so I decided to separate the various issues out and automate as much as I could. The result is a Google Docs spreadsheet that I embed on my course web site, which allows me to think in terms of units and lessons, and which automatically sequences lessons on to class meetings and updates me with notes about those specific days (Moadim L’Simcha, vacation days, etc.).

This is an example of one of my syllabi as the students see it:

In Practice

This turned out to be some pro-level spreadsheet work. I like working in spreadsheets. Not everyone likes working in spreadsheets. This link takes you to a “scratch” version of one of my syllabi (you’re welcome to edit it to see changes — this isn’t live. The organization is thus:

  • There is a worksheet for each unit of the syllabus, named sequentially Unit 1, Unit 2, Unit 3, etc. (The best way to create a new unit is to duplicate an old one and replace the information).
  • On a unit worksheet, a few of the columns are automatically filled in. You just have to worry about editing the title of the lesson, the lesson description, and the assignment summary. Everything else is filled in automatically.
  • The integrated view of all the units, sequenced together and lined up to days with notes is the Syllabus worksheet.
  • The Meetings worksheet is just a list of days when the class meets (which I entered manually) and any notes about that day specifically that might be helpful for lesson planning.
  • There are a bunch of “working” sheets that you can look at, but don’t edit — they’re collating and organizing all of the units automatically.

Reflection

This was way more work than it was worth for a single syllabus. But as a tool that I intend to reuse again and again, I’m pretty happy with it and feel good about the investment. It is mildly idiosyncratic, in the sense that it meets my specific needs. But it could be used as a model for other people’s style of syllabus design, separating the schedule from the concepts in a way that makes visualizing the lesson flow much, much easier.

Go to Top