Skip to main content


Showing posts from June, 2013

Confusion about Apps Script Projects in Spreadsheets.

I'm in some confusion about how Apps Script projects work when embedded in a Google Spreadsheet. In my current spreadsheet, when I choose "Script Editor", I see this...

As you can see, there are multiple projects in the spreadsheet. I don't get this. It always happens that when I copy a spreadsheet too... that I end with a "Copy of XXX" and "XXX" Apps Script projects inside the spreadsheet. 
I can add extra projects via the "Create a new project" link but I can't remove projects from spreadsheets. This gets more confusing if both "Copy of Web App n Stuff" and the "Term Week Dates Booking Project" have a doPost () function in them. Which function gets called? 
Why would I want more than one Apps Script project in a spreadsheet?
Why can't I flip a project out to be a standalone Apps Script project?
When making copies of spreadsheets - why do I end up with multiple projects in a spreadsheet?
How might I remove a…

Blogger's Identity Issues

Google want you to be you. They sort of insist. And they'd really prefer it if you, the real you had a Google+ profile. And when creating a new blog on Blogger they try and make you link it to a real person's Google+ profile.

Except there's a problem. If you create blog posts with a regular Google identity when at the University of York ( or anywhere ) then if you leave, all the images from your blog posts disappear and the blog posts look as if they are written by "Unknown". Hardly ideal, in fact, pretty rubbish.

I've written about this issue before. It's caused because Blogger is sort of "held in stasis"... it is not a good fit with Google's other tools and yet it is so popular it would be difficult for them to close it down. And given Google's track record with social tools, I imagine they've been working out how to close it down for a while now.

The Problem
A department wanted to avoid this strange behaviour of Blogger because th…

When Do You Have Your Weekly Team Meetings?

I met with a department recently and they'd made a spreadsheet where they could tell each other when their preferred time to have a weekly meeting would be. When you have lots of people working different hours as they do, it's difficult to choose a single time that doesn't always exclude someone.

They created a sheet for everyone that looks like this.... You can see how complex things can be schedule-wise for just one person.

The problem was that when they tried to calculate when the best time to meet would be, they got this...

...and discovered that there's never a good time to have a Team Meeting - except for Friday breakfast and over lunch on Tuesday.
I think the problem may have been our inability to imagine how to go about solving the problem, mine especially. Using lots of embedded arcane formulas in spreadsheets has always worried me. I imagine this is simple for a spreadsheet expert. 
What we needed to was work out all the available times ( making them numerica…

Converting a Google Document to simple HTML

This article looks at creating an application that converts a Google Document to HTML, saves it in a folder on your Google Drive and then shares that folder on the web.

This tool is meant for those situations where you have to work in HTML but you'd prefer it if you could use Google's fantastic collaboration features on the content first.
What This App Doesn't Do It doesn't try to be too clever. I quite like HTML from back in the 90s when it was simple. Most other systems where you can add HTML don't like you getting too fancy either.

It doesn't try to size the images or even get ALL the document elements like Tables of Contents or Page Breaks, it just does the absolute basic needed to copy the source HTML into Blogger or into your CMS and you will need to do more than a little "fixing up" along the way.

What is really useful about this app is that all the images are now hosted and so you don't need to go through the painstaking process of copyi…

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet.

At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form.

Spreadsheets can be better than Forms for data collection because:

The spreadsheet data saves as you are editing.If you want to fill in half the data and come back later, your data will still be there.The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessaryThe commenting features are brilliant - especially the "Resolve" button in comments.
One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often all look too si…

Creating a Restricted Multiple Choice Form With Apps Script

I was contacted and asked if I could create a Google Form where the user can only check 8 out of 16 checkbox choices. Google Forms can't do this sort of restriction and so I whipped together a quick web application to do this.
I created an Apps Script in Google Drive and then added this code below.

function doGet(e) { var app = UiApp.createApplication().setTitle("Restricted Multiple Choice").setHeight(250).setWidth(500) // Define the grid layout var grid = app.createGrid(16, 3 ).setStyleAttribute(3, 2, "width", "420px").setCellPadding(5) grid.setStyleAttribute("margin-left", "auto") grid.setStyleAttribute("margin-right", "auto") grid.setStyleAttribute("margin-top", "100px") // Create the text at the top var html = '' html += "Restricted Multiple Choice" html += "You can only choose 3 of the items below." var html_widget = app.createHTML(…

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.

The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.

The Web Application The web application, which only checks items in or out and should be used on a phone in conjunction with a QR cod…

Using Spreadsheets INSTEAD of Forms

Google Forms are a great way to quickly collect data into a spreadsheet but what if the data you are collecting is a bit too complex for a simple form to handle?

We've been experimenting with using a Google Spreadsheet, instead of a Form to gather information and finding that this approach has many advantages. We still use a Form to "initiate" the process, and the data gathered from the form is saved in a "central" spreadsheet.

When the form is submitted, the central spreadsheet makes a copy of a "template" spreadsheet. This spreadsheet is more "human readable" than a regular spreadsheet. When the form is submitted, the data is saved as normal, but it also fills in certain values in the copied template sheet, sets the right permissions and mails all the people who need to know about it a link to edit the template copy.

This template sheet has a "form-like" layout, including help ( shown at the right hand side ) and additional to…