A question came in on the Google Apps Script Google+ Community which in part was asking about parsing .csv files into a Google Sheet. I saw the question come in over my phone and knowing it was very achievable directed the person to the Interacting With Your Docs List tutorial on the Google Developers site as a starting point. In particular this tutorial included a function to parse a .csv file using regular expressions. Shortly after +Andrew Roberts kindly pointed out that Apps Script includes a parseCsv method … doh. +Marcos Gomes then pointed out that the tutorial uses the Docs List Service that was deprecated on December 11, 2014 … doh. Given the tutorial I referenced was written in May 2010 it’s not surprising given the updates in Apps Script that it’s now out of date and the tutorial itself should probably be carrying a deprecated notice. One of the really nice things about the Google Developers site is most, if not all, the documentation is released under Creative Commons Attribution 3.0 License. So for my penance below is a reworking of Tutorial: Interacting With Your Docs List CC-BY Google Inc. (Jan Kleinert)
Google Apps Script
Revisiting issuing Mozilla Open Badges with Google Apps Script and Google Sites: Notes on new options
Back in 2012 I shared some of the exploratory work I did issuing Mozilla Open Badges with using a combination of Google Apps Script and Google Sites. This solution had bit of a fudge which required the badge Assertion (the JSON data file behind each individual badge), to be proxied via a web host. It was great to see this post was followed up by David Mullet who was able to add some additional functionality as well as avoiding the badge proxying. Since then I've also revisited Open Badges, this time as an add-on to issue badges from self-hosted WordPress sites. As part of this I learned more about issuing badges and in particular the Mozilla Issuer API. The Issuer API is a JavaScript library which makes it easy for you to let people add badges they've achieved to their badge portfolio in the Mozilla Backpack. Late last year I decided to revisit Open Badges and Apps Script, this time integrating the Issuer API into a Script powered web app, removing the need send people to a Google Site. This wasn't as straight forward as I had anticipated and but in this post I'll cover what is possible.
Why?
So why issue badges directly from Google Apps Script? This was inspired by feedback from a survey we were running at ALT. This was distributed as a Google Form. To help improve the response rate we included a feature to receive an email confirming the respondent's participation in the survey. The confirmation, sent using Apps Script, included a suggested tweet and a digital badge for them to display highlighting participation. Because the badge wasn't in the Open Badge format respondent's couldn't add it to their Mozilla Backpack. I could have of course reused the earlier work I and David have done on issuing badges via Google Sites, but wanted to see if it was possible to remove that dependency and issue Open Badges from a single container bound script or by embedding an Apps Script in Google Sites.
What’s possible?
Looking through the source code of the Issuer API you can see it has two methods for launching a badge claim interface. The default, OpenBadges.issue (code here) is a full screen modal interface created by injecting a iframe. The fallback, OpenBadges.issue_no_modal (code here), adds the badge data to a web form which is automatically submitted to the Backpack to handle. At the time I was exploring this problem, the method for creating html based interfaces in Apps Script was restricted to a sanitised version of HTML which didn’t support the modal/iframe way of doing things. That all changed in December when Google announced an alternative way of serving HTML in Apps Script that skipped most on the sanitisation (see Speeding up HtmlService in Apps Script).
This all sounds promising, directly wrapping the Issuer API JavaScript library in a Apps Script powered web app. Trying this out I hit my first issue:
I'm using Google Apps Script Content Service to generate a badge assertion (1.0.0 spec), example here https://script.google.com/macros/s/AKfycbx5uvVcmmHweZwhIxzO0IAUrUtY_cW88Sz1B-MpquZxopvfyIY/exec?type=assertion&uid=54354354354-2
When trying to issue the badge using the Issuer API the issuer returns a 504 bad gateway example header response. Using Google Apps Script Content Service there are 302 redirects are in place for the badge assertion which end in 200 OK (this is how Google configure their service). Here is example 302 redirect headers and example eventual 200 OK.
Using Google Apps Script Content Service to create badge assertions appears to only affect assertions that include
verify.url(a 0.5.0 spec assertion is issued without issue).A 302 redirect 1.0.0 spec assertion was issued okay e.g. http://labs.hawksey.info/badges/1_0_spec_redirect.json which suggests something else in the headers is causing the 504 bad gateway
…
If you fill in this form with your Backpack email it sent a link to a Google Site where you should be able to claim a badge associated to your email https://docs.google.com/a/hawksey.info/forms/d/1IKfJQGu1spJyTpkPWYL8TlXcRFQxm8gv8ZMATXcVQWU/viewform
I'm still getting a 504
A lot to take in here. Basically Open Badges can use a JSON object for each badge also known as the Assertion. The Assertion spec is at 1.0 which requires a verification url for the badge. The verfication url is the same as the url where the Assertion lives. Here is an example badge generated using Apps Script:
Now if you hit the verify.url: "https://script.google.com/macros/s/AKfycbx5uvVcmmHweZwhIxzO0IAUrUtY_cW88Sz1B-MpquZxopvfyIY/exec?type=assertion&uid=54354354354-2" you get passed through a 302 redirect:
In terms of Badge Verification “eventual 200 OK” is permitted:
The use of the term "eventual 200 OK" is meant to mean that 3xx redirects are allowed, as long as the request eventually terminates on a resource that returns a 200 OK.
Using the Open Badges Validator throwing in the verify.url returns a valid Assertion
So the Mozilla Backpack issue ticket remains open. Currently, as far as I can see, a version 1.0 spec Assertion generated in Apps Script 504s in the Issuer API. If you are desperate to issue Open Badges in Apps Script NOW!!! the fix ain’t pretty … the Open Badges 0.5 spec assertion (bloody nightmare to find this reference …). This is basically where I was in 2012, a badge assertion that doesn’t use a verify.url.
Lets however look at a couple of example workflows for issuing Open Badges using Google Apps Script.
Example: Using a Google Form to Issue Open Badges via a Google Site
Back to the original scenario of giving someone an open badge for completing a Google Form. Here’s an example Form/Sheet you are free to copy/reuse. Things to note are:
- an extra sheet called IssuedBadges; and
- some code in Tools > Script editor (source extracted into a gist) which needs you to go to Resources > Current project’s triggers and add a onSubmit trigger .
The code is hooked into the form submission and records a new badge in the IssuedBadges sheet and generates an email to the recipient to collect their badge (note current issue with duplicate emails from Apps Script). You’ll also see from the code it includes a BASE_URL, this is the place we’ll send people to collect their badge. A Google Site can happily have a container bound Apps Script, but in this example I want to highlight how you can have a standalone script embedded in a Google Site. So our next bit of code is a standalone Google Apps Script which handles the Open Badges Assertion creation and issuing. You can copy and then modify the standalone script here or browse as a gist).
Hopefully the source has enough in-line documentation to make sense of it. Basically the code looks for the type of data it’s returning. If it’s another machine it throws JSON and for humans shows the Mozilla Issuer API which will help the person collect their badge in the url. To deploy this code we need to publish it as a web app. If you haven’t done this before from your copy of the script in the Script Editor select Publish > Deploy as web app which should give a similar dialog to the one below:
A couple of neat things here. For this to work you need to allow the app to ‘execute as me’. This lets the script access the Google Sheets your account can access. This means the script will selectively be able to read the Google Form responses you get. We also allow the script to run for anyone, even anonymously. This doesn’t mean that anyone can start reading all of your data, the script is programmed only to ready specific data and only return a specific interface designed by us.
When you hit ‘Deploy’ this gives you a ‘service url’. You can try this url and you’ll get a browser window with something like:
Back at the script attached to your Sheet/Form you could replace the BASE_URL with ‘service url’ (this line of code). So when someone fills in your form they will get an email to your deployed script which will have the extra bits needed in the link to issue them a badge. This isn’t great UX so instead lets see how you can use the same url in a Google Site, the advantage being you can provide additional information about collecting a badge in a Google Site. Fortunately the Google Developers site has some great guidance on Embedding your web app in Google Sites which should allow you to paste in your ‘service url’.
You can adjust the gadget settings like size and border, and of course add extra information. To see all of this in action enter your email in the form below and you should get an email with a ‘claim link’ to the ‘sandbox’ site (emails won’t be shared or used for any other purpose than sending the claim url).
Wrapping up
So if you’ve made it this far !!!WELL DONE!!! (#epic). Some things to note. This example currently only works with 0.5 spec Open Badge Assertions. Hopefully the 504 issue with Backpack/Issuer API is resolved. Some of you may have noticed that in this example I opted to use the issue_no_modal of the Issuer API rather than take full advantage of the new Apps Script IFRAME/modal mode. The reason I didn’t is there are some open issues with IFRAME and Caja’d version using no_modal Issuer API works perfectly fine (I’ve left the iframe version in the code if you prefer this way).
Enjoy and as always comments to clarify any of this are welcome
Notes on feature request to access Fullscreen API in Google Apps Script
It’s been a while since I've had a chance to blog about a failed attempt to do something. Recently I was trying to get full screen access in Google Drive using Google Apps Script and I couldn't get it to work resulting in this feature request (star to vote ;). In this post I want to share some of my thought processes and highlight why this would be a useful feature.
In the middle…
One of the nice features of Apps Script is access to custom dialogs and interfaces in Docs, Sheets and Forms. These can be generated using the HtmlService, which enables you to write very rich and interactive interfaces. Until very recently all the Html you created went through Google’s Caja Compilier. Caja created a lot of headaches for Apps Script developers striping and reformatting html tags and restricting what you could do. For example you could forget using SVG (scalable vector graphics) making it impossible to use well established visualisation libraries like D3.js.
However, in December the Google App Script team announced Speeding up HtmlService with the IFRAME sandbox mode. With this there is now the option to skip Caja sanitisation and a whole host of modern browser-based application deployment. For example here’s Román Cortés 1kb Christmas Tree in Apps Script (I've tipped it over the 1k mark as it was throwing an error in Chrome. - the source code for this project is here). Huh turns out with blocks can't be used anonymously, as script owner this works fine :(
The IFRAME mode should not be confused with the ability to use <iframe> to embed your published scripts. This isn't currently possible for security reasons and there is an issue ticket for it.
In the beginning…
My interest in using custom visualisations in Google Sheets stems back to 2011, my first attempt wrapping Protovis SVG graphs, the precursor to D3.js, as a Google Spreadsheet Gadget. Alas Spreadsheet Gadgets got deprecated in the ‘spring clean of 2012’. One of the big advantages of Spreadsheet Gadgets was you didn’t have to publish the data to be able to generate a visualisation, (a route I use for my TAGSExplorer tool). Unfortunately I don’t have a screen shot of how the Protovis Gadget rendered so you’ll have to make do with a later version which switched to D3.js:
With the new IFRAME mode there is an opportunity to re-explore how custom visualisations and analytical tools could be integrated in Sheets as well as Docs and Forms. Bruce Macpherson has already shown an example of D3.js in the Sheet sidebar:
CC-BY-SA Bruce Mcpherson - D3.js in the Sheet sidebar
With this we've come almost full-circle with the ability to create custom visualisations around data in Google Drive without the need to publish it. One limitation here is the amount of screen space you can play with. Sidebars are restricted to 300px wide. You can use dialog windows but these would have to be a fixed size and as far as I'm aware there are no browser detection methods for factor in the available screen size. But lets not forget that I'm predominately talking about scalable vector graphics here and most modern browsers now support the Fullscreen API (Fullscreen API spec). To see this in action embedded below is an iframe of this page, which if supported by your browser, will let you to pop it to fullscreen using the button (I'm just using a raster image to keep with the Xmas theme ;).
So using Apps Script we could have fixed size interfaces with the ability to stay in Docs, Sheets or Forms interface but pop to fullscreen … Well as you have probably already noticed from the post title nope. Here’s a Sheet where I’ve used the same full screen code in a custom dialog. Clicking the ‘Image Test’ link in the spreadsheet opens the dialog but while the fullscreen script runs nothing happens. Initially I thought it was something wrong with my code but trying a YouTube embed within a custom dialog resulted in now full screen option being included.
In the future…
Having access to Fullscreen API would be one way to get us back to 2011 and there may be other feature requests around embedding custom interactive graphs in Google Drive. If you’d like access to the Fullscreen API in HtmlService here’s the feature request.
Oh and happy holidays ;)
Dynamically remove Google Form options after they have been selected by someone or reach defined limits
+Tom Smith kindly reminded me that there are existing Google Form Add-ons to achieve a similar result without having to mess with code. So you might want to have a look at Choice Eliminator by Bjorn Behrendt and formLimiter by Andrew Stillman
You can imagine the scenario, you’ve got a Google Form perhaps setup as a booking form and a select option for picking timeslots. As the slots fill up you’d like the option to be removed. I was surprised not to find an example of this so here is a rough sketch of some code that might do the job. We start with a basic form with a ‘choose from list’ option:
and in the linked Google Sheet we have an extra sheet to keep tally:
In this sheet I’ve got a column with quotas for each of my select options and a calculation for how many are left =B2-COUNTIF(responses!B:B,"="&A2)
To dynamically modify the Google Form we need to add some code. Here’s a basic snippet that reads our options and quotas and rewrites the select options (note the inline comments for bits you’ll need to edit):
function availableSlots(){
var form = FormApp.openByUrl('URL_OF_YOUR_FORM'); // TODO add your form url
// need to read what slots are available
var slots = SpreadsheetApp
.getActiveSpreadsheet()
.getRange("slots!A2:C10")
.getValues(); // TODO make sure getRange matches your quota range
var choice = [];
// loop through our available slots
for (s in slots){
// test if slot still available
if (slots[s][0] != "" && slots[s][2] > 0){
choice.push(slots[s][0]); // if so we add to temp array
}
}
var formItems = form.getItems(FormApp.ItemType.LIST); // our form list items
// TODO assumption that first select list is the one you want to change
// change formItems[n] if you have more than one select list
// and we just rewrite all the options to ones that are free
formItems[0].asListItem().setChoiceValues(choice);
}To enable this you need to use the Tools > Script editor in you Google Sheet and then Resources > Current project’s triggers. At this point to may be prompted for authorisation to read the data and change the form. Once that’s done add the availableSlots function to trigger on form submit.
If you’d like to shortcut all but this last step make a copy of this template.
Notes
Some things to be aware of. If you have concurrent users submitting the form someone may sneak in. Apps Script has ways of preventing this but not as far as I’m aware for Google Forms. It’s worth remembering that do don’t need to use Google Forms at all for getting data into a Google Sheet and if you can host your own HTML form you can make POST/GET requests. If you develop this example further I’d be interested to hear about it. Enjoy!
Allow anyone to add videos to a YouTube playlist from a Google Form using Google Apps Script
In the Apps Script G+ Community Scott Marquardt asked:
Youtube API question.
Argh, I can't find a single example of anything like this. With my current knowledge, I have a hard time inferring from the API reference documents alone, to Apps Script.
My goal is a script to add an item to a Youtube playlist. My intended workflow is a Google form to enter video IDs into a Google sheet, with a triggered script adding each new video ID to a hard-coded playlist.
I'm unashamedly panhandling here, on behalf of what would be a really valuable use case in a dozen of our special education schools.
I agree with Scott that the Apps Script Advanced Services documents (here’s YouTube) don’t give you much to go on. The solution is actually relatively simple once you get your head around the shape of the data required. So here is a form linked to this playlist (I’ll turn this off as soon as it starts getting spammed). In the spreadsheet receiving responses the Tools > Script editor has the following code (in the script you’ll see where you need to hard code the playlist you want to add videos to):
Update: Scott asked about adding notes/comments to playlist items and I've updated the code to do this to the gist
To get this working there are a couple of hoops to jump through. As the YouTube API is an Advanced Service and it must be enabled before use. For this project to do this you need to be in the Script Editor then:
- Click Resources > Advanced Google Services…
- Scroll down to YouTube Data API to turn it on then click the ‘Google Developers Console link:
- In the Google Developers Console find and turn on the YouTube API. After it’s enabled you can close the Console window
You can only interact with YouTube Channels the account authorised to run the script is the owner of (see related post by me on this).
The final step is to setup the script to trigger when the form is submitted. To do this while still in the Script Editor select Resources > Current project’s triggers and add a run addVideoToPlaylist event on form submit.
If you would prefer to not do all the code copy and pasting you can File > Make a copy of this sheet (you’ll still need to enable Advanced Services and the form submit.
There’s obviously lots more you could do with this script like putting submissions into a moderation queue but I’ll let you go and play with those ideas.
Google Apps Script: The authentic{ated} playground (3 things that make Apps Script rock) #DevFest
I was recently invited to London to talk at the GDG DevFest London. The idea for my talk was:
With a pre authenticated cloud-based ecosystem Google Apps Script makes it possible to integrate into other Google services with a couple of lines of code. This turns Google Drive into a rich playground for custom reporting using Google Sheets as a data interface. In this session there will be a quick Google Apps Script 101 highlighting some of it’s main features and affordances. This will be followed by a demonstration of how Google Apps Script makes it easy to combine Google Analytics with other data sources such as Twitter and do many more playful things.
As part of this I highlighted 3 features of Apps Script I find particularly powerful. These were:
- Custom UI – creating dialogs and interfaces integrated into Google Docs, Sheets and Forms.
- Triggers (in particular timed and POST/GET) – there are a number of different triggers you can use in Google Apps Script but the ones I lean on the most are timed triggers and open scripts to POST/GET requests. Timed triggers let you run scripts on intervals even when you are not at the keyboard. The particular power of publishing a script to handle POST/GET requests that I like is that the script can run as you of the person accessing the script. Build in features also let you restrict access so that the script can only be run by you, someone on your domain, anyone and anyone even anonymously.
- Integration – there is some overlap with the previous two but given the potential worthy of note. In other platforms developers it’s sometimes necessary to do a lot to setup the platform and prepare it for other people to use. Apps Script comes already integrated with a number of Google products which means in a couple of lines of code you can access other services. As a demonstration you can see below that with Apps Script in one of line of code you can get a persons unread email count … plus a bunch of other Gmail data.
For what it’s worth slides embedded below:
Converting .csv or Google Sheets into Wikipedia tables
You’ll find lots of blog posts explaining how to do this the other way, converting a table from Wikipedia into a spreadsheet. I even cover this myself in Feeding Google Spreadsheets: Exercises in using importHTML. This post looks at taking a .csv or a spreadsheet and formatting it as a Wikipedia table.
I’ve been on a bit of a Wikipedia journey expertly guided by a friend, Simon Knight (@sjgknight) … who happens to be Wikimedia UK Vice Chair. The journey has included Wiki ShootMe which has been covered by Alan Levine (@cogdog) and a wonderful Teaching Wikipedia Editing session you can still watch from Connected Courses.
One of the great things about Wikipedia is there is no shortage of information on how to do stuff. Before jumping in I had a quick look for some existing tools and came across this article on Importing (converting) content to Wikipedia (MediaWiki) format. Listed is the Spreadsheet-to-MediaWiki-table-Converter. This is some code you would more normally run on a server possibly used as a library within another project. Wanting something simpler I looked for and found guidance on creating a sortable table. The guidance helpfully includes an example copied below:
{| class="wikitable sortable"
|-
! name
! data
! more data
|-
| cats
| 273
| 53
|-
| dogs
| 65
| 8,492
|-
| mice
| 1,649
| 548
|}And here is what it looks like:
| name | data | more data |
|---|---|---|
| cats | 273 | 53 |
| dogs | 65 | 8,492 |
| mice | 1,649 | 548 |
Hopefully you can quickly see the pattern, you start with the column headings, one per line of wikicode and then each of the rows again one value per row for each cell. This type of pattern is easy to code … if you can code … … if you want to code. As I was composing my first line I remembered an old project Templated Export for Google Spreadsheets:
Templated Export allows users to reshape and selectively publish data from Google Spreadsheets in custom formats. The tool is powered by Google App Script which allows integration with Google Spreadsheets using existing authenticated access. Example uses include: turning data from a spreadsheet into a RSS feed and publishing data from a spreadsheet in JSON format to be used by other tools/services. The framework for creating a custom wrapper for your data means there are many more opportunities.
Templated Export lets you customise use a basic reference system to create you own output pattern:
There is some learning curve using this and original post has details on setting this up and using it. The basic concept is you use a cell reference as a way to build up your pattern. You can save your templates which means if you update your data in your Google Sheet you can open the Templated Export interface and download it again or use a saved ‘published url’.
Templated Export hasn’t been touched for two years and is perhaps a candidate for some ‘modern’ Google Apps Script techniques such as publishing as an Add-on. If the thought of playing with Google Apps Script doesn’t take you fancy, Templated Export is based on a feature of OpenRefine called Templating Exporter. OpenRefine can be downloaded and run from your desktop and can handle a variety of data file formats.
So go forth and fill Wikipedia with lovely tables for everyone else to reference in ‘Wikipedia table export’ posts ;)
TwtrService Example: EasyTweetSheet (lets you send tweets from Google Sheet)
I previously posted about TwtrService: A Twitter API client library for Google Apps Script which makes it easy to interact with Twitter from Google Drive applications like Google Sheets. One of the nice things about TwtrService is that once you setup a connection to Twitter you can use it many times in different projects, basically allowing you to do stuff in one line of code. In the post I said I’d share some of the examples of things I make so here is the first one, EasyTweetSheet.
What it does
At the Association for Learning Technology we organise lots of events. We only have a small staff team so having someone sending out tweets during the event can be a problem. We could use a Twitter client like Tweetdeck or Hootsuite to schedule tweets during the day. One issue is if something goes wrong like the livestream not working or a session starting late you can look a bit silly. The solution was to draft our tweets in a Google Sheet and have a link we click when we want the message to be sent. Below is a screenshot for the one we used at this year’s ALT Annual Conference:
How to get you own copy working
- Open this copy of the EasyTweetSheet templateand File > Make a copy
- In your copy open Tools > Script editor and follow the instructions
- Start filling the ‘text’ column with what you want to tweet which should enable the ‘tweet’ link
IMPORANT: If you’ve used my other tools like TAGS this template will use the Twitter account you used to set it up. To use a different Twitter account to send the tweets from
replace YOUR_CONSUMER_KEY and YOUR_CONSUMER_SECRET in lines 34-35 of the Script editor code with your Twitter application key/secret. When you Run > setup switch back to the Sheet view and follow the instructions.
Enjoy!
TwtrService: A Twitter API client library for Google Apps Script
As part of the latest release of TAGS (Twitter Archiving Google Sheet) I moved a lot of the code into a Google Apps Script Library. Libraries are a great way to bundle and release code allowing you to write your own classes and methods. To see some of the functionality already being developed by the Apps Script community you should have a look at the Google Apps Script Samples site and the excellent work Bruce McPherson has done which includes a EzyOauth2 library.
One of the things you can do with libraries is wrap one library into another. When rewriting TAGS it made sense to strip out a separate Twitter client library that I and others could use in different projects. Based on the work by Arun Nagarajan at Google, TwtrService provides access to Twitter's REST API. The work I’ve done is to add some UI and configuration methods to try to streamline the authentication flow. As part of this developers can offer authentication routes using their own Twitter application or use an application created by users. This particular angle is a result of one of the design principles for TAGS, that every copy of the template should use a Twitter application owned by the user. The reason behind this is to distribute the risk. If Twitter were to suspend my data access because a TAGS user abused their API it would suspend access for all TAGS users. By requiring TAGS users to register their own application with Twitter the responsibility to abide by Twitter’s terms of service lies with them. So in TAGS the auth flow looks like this
The result is hopefully a flexible library that developers can integrate into their own projects or by getting users to register their own.
Over the next couple of weeks I'll be sharing some examples applications we've developed at ALT. In the meantime this post serves as a basic introduction to TwtrService and covers:
Overview of TwtrService
The TwtrService library for Google Apps Script centrally stores your Twitter access details allowing them to accessed from multiple script projects without the need for re-authentication. TwtrService is designed to allow you to directly use the Twitter’s v1.1 REST API GET and POST methods. For example to return Twitter search results for the search ‘Google Apps Script’ you would use:
var data = TwtrService.get('https://api.twitter.com/1.1/search/tweets.json', {q: 'Google Apps Script'});
The url string can also be abbreviated to:
var data = TwtrService.get('search/tweets', {q: 'Google Apps Script'});
Additional options can be passed in the parameters array. For example to return 100 results for the same search you would use:
var data = TwtrService.get('search/tweets', {q: 'Google Apps Script', count: 100});
The project key for this library is MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and the TwtrService methods are documented here.
To use the Twitter REST methods TwtrService first needs authenticated access. TwtrService has some built-in methods to do this detailed below. Once a user has authenticated access the TwtrService library stores these as User Properties. This means when a user has authenticated once with TwtrService using the library in another container-bound or standalone Apps Script immediately gives them access to Twitter API results using the get/post methods. In terms of security User Properties are limited to the effective user of the current script.
Usage
Quick start: Personal access
If you would just like to use TwtrService for your Apps Script projects the easiest way to get started is to register a Twitter application and enter it’s details on this page (if you are interested here is the source code for the interface).
Note: If you are already a user of TAGS you’ll already be able to use TwtrService without the step above.
In your Apps Script project you’d like to use the Twitter API in the Script Editor window use Resources > Libraries and add the service using the project key MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh.
In your project you can now use the TwtrService.get() and TwtrService.post() methods. The documentation for get() is detailed below (post is the same but uses HTTP POST):
get(string url, Object parameters)
GET wrapper for request to the Twitter REST API. For full documentation of API method endpoints and parameters see https://dev.twitter.com/rest/public. For example to get last 100 tweets containing 'Google Apps Script': var data = TwtrService.get('search/tweets', {q: 'Google Apps Script', count: 100});
Arguments:
| Name | Type | Description |
|---|---|---|
| url | string | Twitter REST API resource url. This can be either long form e.g. https://api.twitter.com/1.1/search/tweets.json or abbreviated e.g. search/tweets |
| parameters | Object | additional API parameters as detailed in the Twitter REST API documentation e.g. for search results a search string and count is specified by {q: 'Google Apps Script', count: 100}. |
Return Values:
| Type | Description |
|---|---|
Object | API response in JSON format. |
Quick start: Personal access in Sheets and Documents
If you would like to replicate the TAGS authentication flow where users enter their Twitter application key/secret TwtrService comes with a number of UI methods. For TAGS the following code is used:
/**
* Launches key/secret and auth flow
*/
function setup() {
if (TwtrService.isUserConnectedToTwitter()){
var result = Browser.msgBox("Twitter Authorisation",
"You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?",
Browser.Buttons.YES_NO);
// Process the user's response.
if (result == 'yes') {
// User clicked "Yes".
TwtrService.showTwitterKeySecret(SpreadsheetApp);
}
} else {
TwtrService.showTwitterKeySecret(SpreadsheetApp);
}
}
/**
* Used as part of setup() to process form data
*/
function processForm(formObject) {
TwtrService.setUserKeySecret(formObject);
TwtrService.showTwitterLogin(SpreadsheetApp);
}Quick Start: Shared Twitter Key/Secret
The earlier examples have assumed the user registers their own Twitter application. For scenarios where you would like to have the option for users to have authenticated access using a dedicated Twitter API key/secret it is possible to initialize these values. An example application code can be found here which is also deployed here.
Similar to earlier examples once a user authenticates access with your key/secret as long as these values are also initialized in other script projects the user will have access to the Twitter API via TwtrService.
Instructions for creating a Twitter Application to use with TwtrService
TwtrService requires you to have a registered Twitter application. If you are If you haven’t already done this here are some steps you need to get started:
- Register for an API key with Twitter at https://dev.twitter.com/apps/new (if you've already registered for a TAGS sheet you can reuse your existing API Key and Secret).
- Name, description and website can be anything you like
- Important Include the Callback URL https://script.google.com/macros/
- Read the 'Developer Rules of the Road' before clicking 'Create your Twitter application'
On the Twitter site your application should include a ‘Keys and Access Tokens’ tab which includes the Consumer Key (API Key) and Consumer Secret (API Secret) you’ll need.
Need a better Twitter Archiving Google Sheet? TAGS v6.0 is here!
Amazing to think TAGS has been going for over 4 years now. Version 6 is a major code rewrite and the most important new feature is a better setup processes. No more digging into the script editor to run functions, no more entering your Twitter API key and secret each time you create a new archive. Both these things are history thanks to some base code released by Arun Nagarajan at Google. Now you enter/register for a Twitter API key and secret once and each copy of TAGS you make will remember these. This is made possible by incorporating this functionality as a custom Apps Script library I’ve called TwtrService. TwtrService makes it easy to make calls to all of Twitter’s API and I’ll be explaining how it works in another post.
Version 6 comes with some other new features. The one that was most requested was archiving favourited tweets. There was a quick hack version of TAGS that did this but was limited to the last 200. Now when you setup a favourite archive you can get up to the last 3,000 favourited tweets. Another option with TAGS v6.0 is to use Google’s new version of Sheets. This gives TAGS more capacity and performance. One issue however with new Sheets is it isn’t very stable with the Google Visualisation API which is used in TAGSExplorer.
With TAGS v6.0 I’ve also created a dedicated support site. So if you have any questions or need help head over to http://tags.hawksey.info where you can also get the latest version of TAGS.