Google Script, Twitter API & Google Maps

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 […]

Trump Dump Data & Playing with TAGS

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) […]

Google Script Folder Copying Postmortem

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 […]

Social Work Digital Portfolio – Google Docs Style

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 […]

Making Google Scripts & Google Sidebars Talk

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

Setting Cell Values – Google Forms/Sheets Workflow

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 […]

Importing CSV into Google Sheets via Google Script

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.

Rampages Stats vs My Own Data

flickr photo shared by NASA on The Commons with no copyright restriction (Flickr Commons) It’s that time of year when you try to prove to your institution that the work you do matters . . . and I am prepared to make it rain datums.1 I’m not sure how valuable this will be to others but who knows and it helps me to write it down. Currently, my former team of 5 is now just me. So this stuff is sole mio23 and compasses everything from manual password resets to full on custom development work with faculty. Can you tell when the semester started? That’s an increase of abut 2,400 sites in about 3 weeks. I’ve got our weekly Twitter/Google Sheet spitting this out and writing it to a spreadsheet so this data was handy. I’ve been playing with logging data for a variety of reasons. For instance I now tag my rampages support emails in GMail and that logs them to a spreadsheet each night. I’m at least mostly consistent doing that because it’s a very light weight action on my part. I can then get an idea how stuff is really playing out rather than simply my perception of things (although that matters too). The chart above represents rampages email support over the last 30 days as of […]

Custom Web Form to Google Sheets

Sometimes the options given in Google Forms just won’t quite work for what you want to do. Maybe you want a particular look, or an interaction, or whatever that Google Forms just won’t do. Luckily, it’s not too hard to make a custom form that can do whatever you want and still has the ability to write the submitted data to a Google Spreadsheet and the form HTML is still served by Google. The following steps should get you up and running and comments in the scripts should provide additional details. Make a new spreadsheet in Google Sheets. Go to Tools>Script Editor Select all that stuff and replace it with the content below. Replace the string of ****** with the ID of your spreadsheet. Then save it. If you get any permissions prompts approve them. Make a new HTML page (File>New>HTML File) and name it index.html Select all and replace it with this.1 Save it. To make sure things work, let’s publish it (Publish>Deploy as Web App). Now go to that URL and submit something and see if it goes to the spreadsheet. If so, great. Now you can start customizing the form to reflect your needs. This form should now write to a spreadsheet like this. Do keep in mind that each form field you want to write to […]

28

Google Folder to Google Doc Script- The Video

Because I really thought the script to take Google Folder contents and automatically generate a document with headings that match the folder names and automatically linked source documents1 was far cooler than anyone realized . . . I decided to make a poor quality video chastising you and proving the interestingness beyond doubt. Keep in mind that this could be customized to do far more interesting things depending on desired outcome and how you wanted to manage stuff. This is one of those places where I think the technology really has a chance to do something useful. No one wants to spend hours updating a CV/resume every year or two. Instead you could spend minutes spaced out and get all the grunt work down automatically. Google Docs also gives the option to publish the results to the web or download it as Word or PDF. The document and files can be as open or closed as you want. 1 It certainly rolls of the tongue . . .