Showing posts with label Google Documents. Show all posts
Showing posts with label Google Documents. Show all posts

Wednesday, October 29, 2014

The Problem With Google

I'm too old to be fan of technology, but I quite like lots of it, and you can't argue that Google have definitely taken the lead on collaboration. At the core of all its products is the idea that what you are working on, you will want to involve other people, as collaborators, as commenters, as mentors or viewers.

But Google's model of collaboration is all wrong. Or rather, we've adopted Google tools at the university and although they provide the best tools for collaboration, their model of collaboration is hurting us. 

Google's model of collaboration best matches a small business and individual. This is reflected in how Google Drive works. 

For example, in Google Drive, if you create a file, only you can delete it. That's great isn't it? Except because a file is yours, when you leave the university, unless your admins move ALL your files to someone else, they're gone. 

Before leaving the university, you could individually make someone else the owner of one of your files, like this...


But that is, to put it mildly a bit of faff... and if you put your files in a folder and make someone else the owner of the folder, the files still disappear when you leave ( the files don't inherit ownership from the folder ).

And then, you might get fancy and think you could create a solution with Apps Script.  So I tried that. My idea was to create a "dropbox" and a script to watch that dropbox and when a file is added to it, make a copy ( which I, or a departmental account would then own ). It worked fine. Except of course, the script can't delete the original file - because I don't own it. So, I was left with two copies of the file, one I ( or a departmental account ) owned, and the original. Sigh! ( The code below doesn't work by the way ) .




function check_dropbox() {

var dropbox_folder = DriveApp.getFolderById("FOLDER_ID")
var main_folder = DriveApp.getFolderById("OTHER_FOLDER_ID")
var files = dropbox_folder.getFiles()

while (files.hasNext()) {
var file = files.next()
var name = file.getName()

var new_file = file.makeCopy(name, main_folder)
Logger.log("Made a copy of: " + name)
Utilities.sleep(2000)
file.setTrashed(true)

}

}

Maybe you could write a script to simply move your files to someone else. Except you'd have to get  pretty fancy and page through your files if your script would need more than 9 seconds running time. Whilst this might seem like a good idea, you can't transfer ownership of a document to someone at another organisation.


What The Problem Is


The problem is that Google files are so tied to an individual. As an organisation, you need to be able to have documents that aren't tied to individual, but are tied to a role or a department.

And it gets worse as soon as say three universities want to collaborate on a project together. And remember, collaboration is what Google are supposed to be excellent at. Imagine these three universities want to collaborate by sharing documents.  You'd imagine that in the course of a project people might come and go, and ideally, you don't want files disappearing when people move on.

More subtly, you don't actually want any one university to own the files ( even if this was possible, which it isn't ). What is required is a form of shared ownership.

So Come On Google

Collaboration is your thing. I know these are easy problems to solve, but you can't argue that at times, we might not want to an individual, we might want our work to have longevity beyond our involvement and we might want to work fluidly with other organisations. 

At the moment I have someone asking, "We want to set up a five year project and share documents with three organisations. How do we do it with Google Drive?" ... and unless your view of collaboration is one where the documents are fleeting ephemeral things, rather than lasting records, there isn't really a Google-shaped solution that makes a lot of sense.














Creating a Documentation Process With Google Forms, Documents and Spreadsheets.

We wanted to improve the way people at the University request new software and tools. This is a process that requires lots of people's feedback and needs to be very flexible. We need to get software experts to look at it, security teams, the support teams, teaching experts to see if is a good pedagogical match. We need the licensing to looked at and the usability and accessibility. The list is astonishingly long and in these cases it often gets so that your process map just starts to look like infinite spaghetti. No wonder it didn't quite work, infinite spaghetti is always troublesome.

Much of my work involves trying to find a workable solution to a fiendish problem.. it's simplicity hunting. And when working with people around the university it's clear that they really don't want a tool that solves their immediate problem, they want abilities that solve problems like these. This is a very different thing. And besides I personally couldn't create

So, out of necessity I created (an as yet, unfinished ) Apps Script code library, to try and make doing jobs like the one above simpler. The point of this library is not to do anything fancy or specific but just to do those things that frankly Google should have rolled in as features anyway so that new coders could easily just wire their app together with a whole heap less complication.

The code library is called Handy Lumps because that's just what it is. Handy Lumps of code that you can re-use again and again. I won't tell you how to install Handy Lumps library into your code, but you can find that out easily enough. The project id is...

1ykOx87hMWudgdOl3i9XND-zeV8WEieBjVwxcPYG_2iDvX5kd70KpbfIl

So What Does This Example Do, Tom?

In this example, someone fills in a Google Form to request some new software. What it then does is take that information and render it into a Google Document template file, and put it into a folder. Lastly, it saves the URL to the new file in the spreadsheet. It's amazing how many use cases look a bit like this.

It's also amazing how many processes start by looking like something mappable, something with a clear structure but actually are closer to an iterative collaboration. For example, the template that gets created has further questions in, which can of course be added to and bent into the shape that is required. And of course using Google Document +commenting feature you can easily bring someone new into the discussion for their advice and help.


So How Does This Example Work, Tom?

Let's look at the code. First I created a Form and then went to Spreadsheet and chose the Tools > Script Editor menu and added this. I'll explain what it does below.


function onFormSubmit(e) {

  //Get the values in a nice Array
  var values = HandyLumps.row_to_dict(e.range)
  var template_id = "TEMPLATE_DOCUMENT_ID"
  var folder_id = "FOLDER_ID" // Our Responses folder.
  var name = values['Name'] + " - " + values['What software are you requesting?']


  // Create a Google Doc
  var new_file_id = HandyLumps.copy_and_render_to(template_id, name, values, folder_id)
  var new_file = DriveApp.getFileById(new_file_id)
  var url = new_file.getUrl() 

  //Update the spreadsheet with a link to the new file
  var ss = SpreadsheetApp.openById("THIS_SPREADSHEET_ID")
  var sheet = ss.getSheetByName("Form responses 1")//this sheet
  var row = e.range.getRow()
  sheet.getRange(row, 16 ).setValue(url)
 

  MailApp.sendEmail("YOUR-GOOGLE-GROUP-HERE@york.ac.uk", "New Software Request: " + values['What software are you requesting?'], url, {noReply:true})

}

The first thing the script does is turn the row of data into a nice array. This returns an array that looks like this {'timestamp':2014/29/10 10:55:45, 'name': Tom ...etc} It builds this array based on your header names ( and yes, it assumes they are unique for simplicity ) . Doing this avoids the issues with e.namedValues containing multiple items and gives me a simple array I can use later.

Next we tell the script the ids of the template document and into which folder we want the new documents to go.

We then create a new document from a template file. The template file has {timestamp} and {name} tags in which match my spreadsheet headers and get replaced with the values. To do this we use ...

HandyLumps.copy_and_render_to() 

This function returns the id of the new document created, so we then open it with DriveApp and get its URL. ( I did think about returning the File object, but often that's not what I needed anyway so decided on the simplest thing ).

I then use regular Apps Script to save that URL into the same row.

The last line mails a Google Group to let them know a new request has come in.

Ta Da!

There you have it. We've made quite a cute thing in a paragraph of non-scary code copy-and-pastee-style.

I'm all for the current trend to believe that "we all can be coders now" but I also think that the tools themselves could be made a damn sight easier to use before we welcome those brave souls willing to give it try.






Next Steps


More involved versions of the above code create a Google Doc from a template that has code in it, so that new document can show a sidebar ( for example to approve it, or give it a mark out of ten ) that let's someone move the document onto the next step. The data from the sidebar is of course saved into the right row using Handy Lumps functions like this...

HandyLumps.get_row_containing(ss_id,sheet_name, column_letter, match)


In the example above, a document's script might contain...

var doc = DocumentApp.getActiveDocument()
var doc_id = doc.getId()
var result = HandyLumps.get_row_containing("YOUR_SHEET_ID","SHEET_NAME", "M", doc_id)
var row = result[0]
var values =result[1]


... which essentially means that a document knows where to store its new data. And using cute things like Google Document's Named Ranges you can make a sidebar that stores people's textual contributions back into the original spreadsheet. I'll hopefully get to sharing that stuff later.










Tuesday, November 19, 2013

From Survey To Google Spreadsheet To Google Document

Earlier today we were looking over the results of a survey we'd put out with Google Forms. The answers were well thought out, very long and textual and impossible to read in a spreadsheet.

As a group we want to read the responses and share our thoughts about them using the comment feature in Google Documents so I whipped up this script to move the all the data from a spreadsheet to a Google Document.


function document_from_spreadsheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getActiveSheet()
var header_range = sheet.getRange(1,1, 1, sheet.getLastColumn())
var headers = header_range.getValues()[0]

var data_range = sheet.getRange(2,1, sheet.getLastRow(), sheet.getLastColumn())
var values = data_range.getValues()


var doc = DocumentApp.create(ss.getName() + " Exported")
var body = doc.getBody()

for (var h in headers){
h = Number(h)
var header_name = headers[ h ]
var p = body.appendParagraph(header_name)

p.setHeading( DocumentApp.ParagraphHeading.HEADING1 )

for (i = 0; i < values.length; i++){
var row = values[i]
var value = row[h]
var p = body.appendParagraph(value).setHeading( DocumentApp.ParagraphHeading.NORMAL )
body.appendHorizontalRule()

}
body.appendPageBreak()
}
doc.saveAndClose()
}

function onOpen(T) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Export to Google Document", functionName: "document_from_spreadsheet"},
];
ss.addMenu("Admin", menuEntries);
}


There was a little cleaning up to do, to remove any choice-based or numerical data items ( we could of course paste those in as images ) but this code was all we need to start easier on the eye and brain analysis of the responses.



Wednesday, June 19, 2013

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 copying the images into new images, saving them online somewhere, getting the URL of those images and replacing the right image src in your new HTML source. This was a pain for me.

Also, the app doesn't HTML encode the "<" and the ">" so you can add HTML tags like the preformatted tag or whatever, knowing that they'll still be there in the outputted HTML. I use the font Courier New to tell it that I don't want the converter code to touch it, but one of the problems is that ANY HTML that is lurking in "courier new" code is shown, as HTML.

The results can be hilarious. Ah well. I said it would need "fixing" up. Here's an example of the HTML it generates.

The application itself looks like this.


The simple application lets you select which Google Doc you want to convert to HTML and then loops through the document elements and creates a html page.

Any images found are added to Google Drive hosting ( their URLs changed to the new location ) letting you copy the HTML from the "index.html" file and paste it anywhere you need.

And Finally

You can try it on your documents here: https://script.google.com/macros/s/AKfycbyRt8FCEDMtZELx7-E_c3Hjktaaf1qJwRmitfRx48UocIP8ViBA/exec

Or if you'd like to rummage around the code please feel free to improve it.
https://script.google.com/a/york.ac.uk/d/1BWQRHrM589TnsXviuB3Zzws_ShFDF6sEdaO4sg4Eht0kuZx1iou4Xxn2/edit

Thursday, May 16, 2013

Document Sidebar and Menus in Google Docs

For a long time I've been saying that Google need to make sure that their left hand knows what the right is doing. There are pockets of innovation in some products that are notably missing from other products. And whilst I don't advocate insisting that each department should consult with each other department before a beautiful carbuncle can be brought into the world, it's nice when you feel that different Google teams are at least on speaking terms.

One example ( and there are dozens and dozens ) of innovation insularity is the features that are in a Google Spreadsheet. With a little code and ingenuity you can add menus, and pop ups and whole new interfaces to a spreadsheet, but this can't be done in a Google Document. "Why not?" you might ask. And rightly so.

So, I was very pleased when Google announced at Google I/O ( their big developers conference happening right now ) that the features from Spreadsheets were being added to Google Documents. Take a look at this document. It has custom menus and a custom sidebar.


Imagine...

… what you could use those menus for. Imagine what content, or links, or even more clever stuff you might have in the sidebar. It could be pre-created content, by you the lecturer or it could be content that is derived from whatever you are typing. The menus might remind you when the essay deadline was, or email the document to random peers. I've already made some code to save a Google Document to Blogger that saves the images into a publicly hosted GDrive folder. This article was written in Google Docs, not Blogger which is a bit shonky nowadays. Imagine how easy it will be to add a "Save to Blogger" menu item. This is exciting stuff.


Except hold your excitable horses.

The only problem is, that the code to do the things you can do in a Google Spreadsheet is completely different to the code you need to do to do it in a Google Document. The code below shows how to add a menu and a sidebar to your Google Document ( go to the Tools >> Script Editor menu and paste it in, then run onOpen. You will need to authorize it ).

function onOpen() {

// Display a sidebar with custom UiApp content.

var uiInstance = UiApp.createApplication()
.setTitle('My UiApp Sidebar')
.setWidth(250);
uiInstance.add(uiInstance.createLabel('This side bar can contain content that is pre-defined by the lecturer.'));
uiInstance.add(uiInstance.createLabel("It might contain some regular links to useful stuff.

"))
uiInstance.add(uiInstance.createAnchor('It might contain a link to plagiarism', 'http://www.york.ac.uk/integrity/plagiarism.html'))
uiInstance.add(uiInstance.createLabel("The really interesting part, for me, is that it might contain dynamically generated links to stuff of relevence to what you are writing. "))
uiInstance.add(uiInstance.createLabel("What if a document had links to required reading, or a link to your mentor?"))
uiInstance.add( uiInstance.createImage('https://si0.twimg.com/profile_images/3457614642/d6b665b4c213fe02ec28dc3e94db6e0b.jpeg').setHeight(100).setWidth(100))

DocumentApp.getUi().showSidebar(uiInstance);
DocumentApp.getUi()


.createMenu('My Menu')
.addItem('Do something...', 'do_something')
.addSeparator()
.addSubMenu(DocumentApp.getUi().createMenu('My Submenu')
.addItem('One Submenu Item', 'do_something_else')
.addItem('Another Submenu Item', 'do_nothing'))
.addToUi();


function do_something() {
DocumentApp.getUi().alert("This could do something")

}

function do_something_else() {
DocumentApp.getUi().alert("This does something too. It could go and get live weather data and insert it into the document. Ha ha!")
/* THIS DOESN'T WORK
Browser.msgBox("This does something too. It could go and get live weather data and insert it into the document. Ha ha!")
*/
}

function do_nothing() {

DocumentApp.getUi().alert("This does something. It could be anything. It might mail this doc to someone, or add it as a reference to a spreadsheet.")


}



I tried hacking the code into a Spreadsheet. It did sort of work. The sidebar appears as a popup dialog, but the menus don't work at all.



Whilst initially really encouraged that Google were "pulling the strands together" at last and making cool behaviours consistent across their product range, I am a little disappointed that it isn't completely idiot proof ( I have a stakeholder interest in things being idiot proof ).

My guess is that this is a sign of things to come, that the process of innovation consistency is just starting and one day I will be able to add Apps Script to my Drawings and Presentations and whatever else from Google.

Tuesday, March 19, 2013

Improving Ethics Approval in Research With Google Forms and Spreadsheets

I have just met with a colleague who is using Google Forms to improve the Ethics Approval process for research projects. They are taking a slightly different path to the one I might have plumped for, and are creating, on-the-fly Google Documents, filling in the appropriate sections and then sending them to people to add comments to etc.

We thought he may have worked himself into a corner because although there is a function to capture when people submit a form ( onFormSubmit() ), there isn't one to capture if you "allow the user to edit responses" - there's no onFormUpdate().

We created a workaround by having a time-based trigger that just checks to see if the updated_timestamp is greater than the last_checked_timestamp.

The best part, for me, of working with my colleague was when I referred at some point to my JavaScript skills being far from honed and he asked "So is this JavaScript then?" and laughed incredulously. He was happily coding away without even knowing what language he was coding in. Brilliant.

One thing I'm starting notice over the last few weeks and months is how many people are working on processes... and/or process improvement. I keep meeting people with visual flow charts of their ideal worlds.  I wonder how far we should encourage Google Apps hacking ( where you potentially can get almost exactly what you want ) or whether we should be looking at tools that explicitly handle processes, like RunMyProcess ( shown below ) which even have their own tools to design your workflows and a gazillion integration plugins.


But then there's also an overlap with CRM and service desk applications. In most cases there's just stuff that requires stuff to happen that involves people to do stuff - which is pretty much what a CRM might do isn't it?

So, I'm wondering, given that I keep coming across people interested in creating workflows, should I be looking into a workflow lite app or heavyweight state of the art workflow system? Or should we keep crafting ( or crufting ) our own version?

 

© 2013 Klick Dev. All rights resevered.

Back To Top