We had a list of rampages sites in a Google Spreadsheet and wanted to know when they were created. I started to look that up but only managed to do it twice before I gave up and went in search of another way. In this case it took two little bits of code. This first piece is active on our generic site-wide plugin. It adds the blog’s creation date, last updated, and post count to the base JSON data. That’ll be handy in the future if we want to checkup on sites with only one query rather than multiple queries. This second piece is a Google Script that makes a function that I can call in the sheet by typing =getCreationDate(“http://someurl.com/”) The two together answer my immediate problem but the JSON modifications have some long-term value for us and might be useful to someone else.
Google Sheets often thinks it’s smarter than you. That’s helpful at times and irritating at other times.1 The Google Form to Sheets path is one place where that can come up repeatedly and in ways that are hard to see. For instance, if you use the Time entry on forms, Google Sheets tries to coerce it into a date structure while leaving you seeing just the time in the sheets view. That leads to weird and unpredictable stuff if you’re trying to use Google Scripts to interact with that cell data based on what you see. For instance, this is data from the time entry field as seen on the sheet side. Note the two places you might expect to see truth. Now if we get that same data via Google Script . . . Cell data that you’d expect to just be that time element returns as Sat Dec 30 1899. The time is right though. If you manually change the format of that column to plain text things work . . . but it gets re-set on all subsequent submissions. My solution so far has been the following Google Script set to fire on every new form submission. In my case, I’m changing the format of the latest entry in the spreadsheet in columns F and G to […]
Image taken from page 12 of ‘Guide to Cambridge: the town, university and colleges … To which are added, notes upon the villages within ten miles, a map, etc’ flickr photo by The British Library shared with no copyright restriction (Flickr Commons) This is more playing with Martin‘s TAGs in Google Script and then moving it into Google Maps for visuals . . . the TAGS related functions will only work in that context but the purse Google stuff should work in any spreadsheet. Get Twitter Location by Account This function will get the user’s location as defined by their profile. So =getLoc(“twoodwar”) would return Richmond VA. This function will get the lat/long via Google Script. So =getLl(“Richmond, VA”) would return 37.5407246, -77.4360481. Keep in mind if it’s trying to get the lat/lng for someone who put “the Interwebz” as their location like OnlineCrsLady then your lat/lng may not work out that well. So that’ll let us take a chunk of TAGs data.1 and do something like this with Google Maps. I’m not going to break this chunk down quite yet. I did it at very odd hours and it works but it’s not very clean and I know there are ways to do it better.There is no shame in my game but there is some bashfulness. Pretend I’m an […]
Image from page 60 of “Children’s ballads from history and folklore” (1886) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) Driven mad by curiousity after this Matthew Baldwin tweet, I built this little thing. It uses the amazing Martin Hawksey’s TAGS for gathering the Tweets in Google Sheets and then displays it with Vue.js (which I’m sort of learning). It led me to realize that I could extend TAGS without much effort. My first attempt was to write two custom functions to get favorites and retweets. Turns out that was pretty straight forward given all Martin’s work. The TAGS element (TAGS.get) links me into Martin’s library and that’s that. So very easy once you know and then you’re just navigating the Twitter API. Turns out I can do something similar to get the Twitter bio. Initially, I just stuck these in like you’d do normal functions … =getRT(A1) or whatever and it soon ate up all the processing time allowed for my Google Scripts. That caused other things to break. Lesson learned. I then opted to set the value in the script and write that value to the cell. This little script runs on the spreadsheet change trigger and checks to see if there are any blank cells in the Favorites column (column U) […]
Image from page 93 of “Elementary and dental radiography” (1813) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) This is the blow-by-blow documentation of a failure of sorts. Nothing makes me angrier than failing to deliver on something I said I could/would do. I ended up delivering what was needed but the way I had to do it was ugly and time-intensive. All of this happened because of two things- insufficient initial testing and not enough knowledge on my end at a couple of key steps. I’ve got lots of ideas I need to test out now that I’ve learned a few things the hard way but I thought it’d be beneficial to see how I tried to think through this thing when various paths failed . . . and with that welcome to my postmortem. I thought we had the Social Work Google Docs Digital Portfolio thing figured out. I even wrote a blog post. Events coalesced to remind me that I do not know enough stuff. However, I do intend to learn from painful mistakes like this and give you my tears and frustration for you free of charge.1 Initial tests felt a bit slow but nothing too bad. Google Scripts will timeout after 6 minutes but I thought we’d be ok […]
Image from page 589 of “Modern magic. : A practical treatise on the art of conjuring.” (1885) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) Our Social Work program has traditionally done large paper-based student portfolios. They wanted to move to something digital. That led to some conversations about Google Docs and our options there. They needed the ability to- provision a set of folders and documents to individual students allow the students to edit/add to the folders stop editing rights at a certain date make the student folder anonymous for faculty reviewers The solution I ended up coming up with uses a Google Spreadsheet with some custom Google Script. It’s based on a spreadsheet with column A being the student email and column B being the anonymous number or name. You make a parent folder (Social Work 2017) and put the spreadsheet and the folder (student portfolio) you want to copy in the folder. You add the student emails and secret IDs to the spreadsheet. The script is activated via custom-menu element imaginatively entitled ‘Share Files’ and it copies the student portfolio for each student email listed, names it with the addition of the secret ID, and gives the student editor rights to their particular folder and its contents. It also writes the […]
flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons) Sometimes you want a Google Sidebar element to be able to pull variables from a Google Script. The key element is google.script.run.withSuccessHandler. I’m still not entirely sure I have this in my head deeply/correctly but this is functional and might help someone else (even if that someone is just me at a later point in time when I’ve forgotten this). This one sets some default times based on spreadsheet variables stashed elsewhere and then automates some date/time entries using the form sidebar. Sidebar Code Google Script Code The entire sidebar/script setup is below in case anyone cares for some reason. Pick Your Date Start/End Times Start: End: Hide Previous Dates Submit
flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons) Often, people really like the workflows enabled by Google Forms but they’d like it to go one step farther . . . like adding up two submitted items, or running a particular function/formula against the submitted data. You can certainly go in there and manually enter formulas or drag down to apply them to additional cells but that sucks and if you’re automatically displaying this data live somewhere it’s an impediment to a solid workflow. Most importantly, it sets a human to do something that a machine ought to be doing- that is the path to Skynet assuming control. We can and must resist! Subjugate the machines whenever possible! The script below is broken into three parts and shows two different ways to set the value for columns based on data entered in a form. Part 1 – This is pretty much default information that lets the script know which spreadsheet and which page it’s working with. The getLastRow() is really handy for applying this to data as it is entered. This example form writes data to columns A – K. In example one, we do the math internally and just spit the result into the cell. That’s handy for lots of stuff and can be […]
flickr photo shared by OSU Special Collections & Archives : Commons with no copyright restriction (Flickr Commons) As part of a future project, I’m looking for easy, automated ways to push/pull CSV files around. Initially I thought I’d just do the =importdata(‘http://theurl.com/data.csv”) function but I realized that had some drawbacks that made it less ideal which lead to the script below. Using Google Script triggers this script could be set to retrieve a CSV file every X amount of time and write it to a Google Sheet. The upper portion is adapted from this answer. In any case, it opens up some decent automatic options and would keep data fresh for easy access charts and graphs in Google.