Editor’s note: This is a guest post by Thomas Coudray, Amaury de la Vieuville, and Ahmed Bougacha. Thomas, Amaury, and Ahmed attended the Google Apps Script Hackathon in Paris, and in this post they are sharing their creative use of Google Apps Script to render a 3D scene in a Google Spreadsheet. -- Jan Kleinert
Recently, we heard about the Google Apps Script Hackathon arriving in Paris, France. We did not know much about Apps Script - heck, even JavaScript! Perfect occasion to learn something. We spent most of the event hacking around with the ever-growing collection of Google APIs. As a tribute to the folks over at id Software, we settled on one of the most fun (however useless) ways to use it: rendering a 3D scene in a spreadsheet.
The rendering is done using a technique called ray-casting, made popular by the 90s id Software game Wolfenstein 3D. Ray-casting is a really brilliant and straightforward algorithm:
First, we render the background: color the upper (sky) and lower (floor) halves of the screen in different colors. We store the pixel colors in a matrix, the screen buffer:
screen = new Array(SIZE_Y); for (var lin = 0; lin < SIZE_Y; lin++) { screen[lin] = new Array(SIZE_X); for (var col = 0; col < SIZE_X; col++) { screen[lin][col] = colorToString((lin < MID) ? UPPER_BG_COLOR : LOWER_BG_COLOR); } }
Note that we draw the screen only once the buffer is fully colored, to avoid the overhead of coloring cells individually.
Then for each column of the screen:
The trick is in the drawing: the upper and lower halves of the screen are symmetrical in shape, and the only computed value is the display height of the wall. The screen really is just a fancy formatting for an integer array of columns.
The camera is represented using:
We store these 3 values at the bottom of the sheet, to ensure persistence (else, each refresh would bring us back to the start location!).
function Camera() { this.x = CAMERA_X; this.y = CAMERA_Y; this.theta = CAMERA_THETA; this.saveToSheet = function(sheet) { // The player state has to be saved between each frame sheet.getRange(STORE_LIN, 1, 1, 1).setValue(this.x); sheet.getRange(STORE_LIN, 2, 1, 1).setValue(this.y); sheet.getRange(STORE_LIN, 3, 1, 1).setValue(this.theta); }; this.readFromSheet = function(sheet) { this.x = sheet.getRange(STORE_LIN, 1, 1, 1).getValue(); this.y = sheet.getRange(STORE_LIN, 2, 1, 1).getValue(); this.theta = sheet.getRange(STORE_LIN, 3, 1, 1).getValue(); }; ... }
The map is a logical matrix, thus limiting us to discrete boxes for walls: for every cell, there either is (1), or is not (0), a wall:
// starting 10x10 map var S = 10; var map = [ [1, 1, 1, 1, 1, 1, 1, 1, 1, 1], [1, 0, 1, 0, 0, 0, 0, 0, 0, 1], [1, 0, 1, 0, 1, 0, 1, 1, 0, 1], [1, 0, 0, 0, 0, 0, 0, 0, 0, 1], [1, 1, 1, 0, 1, 1, 0, 1, 0, 1], [1, 0, 0, 0, 1, 0, 0, 1, 0, 1], [1, 0, 0, 0, 1, 0, 0, 1, 0, 1], [1, 0, 1, 1, 1, 1, 0, 1, 0, 1], [1, 0, 0, 0, 0, 0, 0, 0, 0, 1], [1, 1, 1, 1, 1, 1, 1, 1, 1, 1] ];
It is also possible to modify the map in real-time: write a character in the boxes you want to swap, then hit Refresh map.
Moving involves adding (or subtracting for backwards movements) to the xy coordinates, using basic trigonometry, but only after checking the validity of the move (i.e. that it will not collide with a wall):
function Camera() { ... this.move = function(distance) { // return whether valid move or not x = this.x + Math.cos(this.theta) * distance; y = this.y + Math.sin(this.theta) * distance; if (isValidPos(x, y)) { this.x = x; this.y = y; return true; } return false; }; } function moveUp() { readMapFromSheet(sheet); // Retrieve the map from the sheet var camera = new Camera(); camera.readFromSheet(sheet); // Retrieve the camera state from the sheet camera.move(0.5); raycast(camera); }
Turning left (respectively right) is even simpler, adding (respectively subtracting) small constants to the camera angle (mod 2 PI):
function Camera() { ... this.rotate = function(alpha) { this.theta = (this.theta + alpha + 2 * Math.PI) % (2 * Math.PI); }; } function lookRight() { readMapFromSheet(sheet); var camera = new Camera(); camera.readFromSheet(sheet); camera.rotate(-0.25); raycast(camera); }
Actual actions (moving/turning) are shown in a menu:
spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var subMenus = [ {name:"Reset",functionName:"onOpen"}, {name:"Refresh map",functionName:"refresh"}, {name:"Move forward",functionName:"up"}, {name:"Look left",functionName:"left"}, {name:"Look right",functionName:"right"}, {name:"Move backward",functionName:"down"}, {name:"Turn around",functionName:"turn"}, ]; spreadsheet.addMenu("Sheetcaster", subMenus);
The ray is cast as follows:
/* * Given a value on the x axis (screen column), * return the ray that will be cast */ function getRay(camera, x) { var cos = Math.cos(camera.theta); var sin = Math.sin(camera.theta); // from -1 to 1: 0 being when x is the middle column var k = ((SIZE_X / 2) - x) / SIZE_X; return new Vector_( cos / 2 - k * sin * K_FOV, sin / 2 + k * cos * K_FOV ); }
Moving the ray is the most involved step:
while (!hit) { // Next potential wall is on the x axis if (dist.x < dist.y) { // Distance from the camera, delta: / Distance between each horizontal wall along the ray dist.x += delta.x; // step.x is either 1 or -1, depending on the ray direction mapCoord.x += step.x; hit = readMap_(mapCoord.x, mapCoord.y); } else { // Next potential wall is on the y axis dist.y += delta.y; mapCoord.y += step.y; hit = readMap_(mapCoord.x, mapCoord.y); } }
The height of the drawn column is nothing fancy: the further the wall, the smaller-looking the wall, hence the smaller the height of the column.
Again, nothing really complicated. However, the simplicity of this wall-height technique is the reason behind its major caveat: there is no clean way to look up or down: you can only turn left or right, and move forward or backward.
Displaying the rendered image is done using a spreadsheet. Each cell becomes a small square pixel, its color being the background color of the cell. We pass our scren buffer matrix to the handy setBackgroundColors:
sheet.getRange(1, 1, SIZE_Y, SIZE_X).setBackgroundColors(screen);
As you probably noticed, the low display density makes the sharp, jagged, edges really visible. Fear not, reader, for we also implemented anti-aliasing!
The anti-aliasing algorithm is even simpler:
When the runs are really small (< 5 columns), we attenuate the gradient intensity, as it would only add another pixel above (below) the column, thus rendering the antialiasing utterly useless.
Real-time was not an objective, the main problem being controlling the player/camera. Scripted movements should however be quite easy to implement with a fixed duration loop, restarting itself using an Apps Script recurrent time-driven trigger (a minute-long loop, repeated every minute). This is left as an exercise to the reader.
Please feel free to copy the script and walk around this Apps Script virtual world.
var oAuthConfig1 = UrlFetchApp.addOAuthService("googleProfiles"); oAuthConfig1.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https:// www.google.com/m8/feeds/profiles"); oAuthConfig1.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken"); oAuthConfig1.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken?oauth_callback=https:// spreadsheets.google.com/macros"); oAuthConfig1.setConsumerKey(ScriptProperties.getProperty("Consumer_Key")); oAuthConfig1.setConsumerSecret(ScriptProperties.getProperty("Consumer_Secret")); var options1 = { oAuthServiceName : "googleProfiles", oAuthUseToken : "always", method : "GET", headers : { "GData-Version" : "3.0" }, contentType : "application/x-www-form-urlencoded" };
var theUrl = ""; if (nextUrl == "") { theUrl = "https://www.google.com/m8/feeds/profiles/domain/" + domain + "/full?v=3&max-results=" + profilesPerPass + "&alt=json"; } else { theUrl = nextUrl; }
if (theUrl != "DONE") { var largeString = ""; try { var response = UrlFetchApp.fetch(theUrl, options1); largeString = response.getContentText(); } catch (problem) { recordEvent_(problem.message, largeString, ss); } }
var provisioningJSONObj = null; var jsonObj = JSON.parse(largeString); var entryArray = jsonObj.feed.entry;
for (var i=0; i<entryArray.length; i++) { var rowArray = new Array(); rowArray[0] = ""; rowArray[1] = ""; rowArray[2] = ""; try { rowArray[0] = entryArray[i].gd$name.gd$fullName.$t; } catch (ex) {} //fullname try { rowArray[1] = entryArray[i].gd$name.gd$givenName.$t; } catch (ex) {} //firstname try { rowArray[2] = entryArray[i].gd$name.gd$familyName.$t; } catch (ex) {} //lastname
var updateRow = getNextRowIndexByUNID_(rowArray[3],4,stageSheet); var valueArray = new Array(); valueArray.push(rowArray); var outputRange = stageSheet.getRange(updateRow, 1, 1, 12); outputRange.setValues(valueArray);
} else { // COPY CHANGES TO "PRODUCTION" TAB OF SPREADSHEET var endTime = new Date(); setSettingFromArray_("LastPassEnded",getZeroPaddedDateTime_(endTime),settingsArray,setSheet); if (parseInt(getSettingFromArray_("StagingCopiedToProduction",settingsArray)) == 0) { // THIS DOES A TEST-WRITE, THEN A "WIPE," THEN COPIES STAGING TO // PRODUCTION var copied = copySheet_(ss,"Staging","Employees"); if (copied == "SUCCESS") { var sortRange = empSheet.getRange(2,1,empSheet.getLastRow(),empSheet.getLastColumn()); sortRange.sort([3,2]); // SORT BY COLUMN C, THEN B // RESET SETTINGS setSettingFromArray_("NextProfileLink","",settingsArray,setSheet); setSettingFromArray_("LastRowUpdated",0,settingsArray,setSheet); setSettingFromArray_("StagingCopiedToProduction",1,settingsArray,setSheet); } } } // end if "DONE"
Editor's note: This has been cross-posted from the Google Code blog -- Jan Kleinert
Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services. If you want to learn more about Google Apps Script, collaborate with other developers, and meet the Apps Script team, here’s your chance! We will be holding an Apps Script hackathon in Washington, DC on Wednesday, March 7 from 2pm - 8pm. After we cover the basics of Apps Script, you can code along with us as we build a complete script, or you can bring your own ideas and get some help and guidance from the team. There will be food, power, and Apps Script experts available to help throughout the day. Just bring your laptop, ideas, enthusiasm, and basic knowledge of JavaScript. Check out out the details of the event and be sure to RSVP to let us know you’re coming.
What’s the difference between reality and theory? In theory, there is no difference. But reality often imposes unanticipated constraints on developers. These may come in the form of bandwidth restrictions, memory limits, timeouts, or other requirements of the systems that interact with your application.
My team recently built an application that helps us analyze the scheduling and usage of conference rooms at Google. We use the new Calendar API v3 on Google App Engine to read the rooms’ schedules, which we combine with actual occupancy data to calculate utilization and other metrics.
As you might imagine, Google has a lot of conference rooms (I believe the last official count was “more than twelve.”) And many of the rooms seem to be booked fairly solid. That means we need to read a lot of data from Calendar. So much, in fact, that our queries time out if we try to read an entire calendar at once. But the API team anticipated “Google scale” use and designed a mechanism that allows us to retrieve data in batches.
The idea is simple. When you create a request, you specify the page size: the maximum number of results you’d like Calendar to return in one batch. Calendar returns the data you requested, along with an opaque page token, which you can think of as a bookmark. To retrieve the next batch of data, you ask the API for the next page token and include the new token in your next request. The page token keeps track of the results you’ve already seen, so Calendar can send the next batch each time. You repeat this process until you’ve exhausted all the results.
Here’s how we did this in Java:
public void getRoomEvents(String roomEmail) throws IOException { // Create a request to list this room’s events (see code, below) Calendar.Events.List listRequest = getListRequest(roomEmail); do { // Retrieve one page of events Events events = executeListRequest(listRequest); List eventList = events.getItems(); // Process each event for (Event event : eventList) { processEvent(event); } // Update the page token listRequest.setPageToken(events.getNextPageToken()); // Stop when all results have been retrieved } while (listRequest.getPageToken() != null); } // Create a request to list the events for a room private Calendar.Events.List getListRequest(String roomEmail) throws IOException { return calendarClient.events().list(roomEmail) .setMaxResults(1000) // Limit each response to 1000 events .setPageToken(null) // Start with the first page of results // Return an individual event for each instance occurrence of a // recurring event .setSingleEvents(true); }
We call getRoomEvents() for each room, using the room’s email address to identify it to Calendar. (You can retrieve events from your own calendar by substituting your own email address.) Then getListRequest() creates a request that we will send to Calendar. The request asks for a list of up to 1000 events from the room’s calendar.
getRoomEvents()
getListRequest()
The remainder of getRoomEvents() is a loop that executes the request, processes the results, and updates the page token in preparation for the next request. The loop continues, retrieving and processing each subsequent page of results, until the entire list has been returned. The call to getNextPageToken() indicates the end of the results by returning a null value.
getNextPageToken()
By paginating our requests we avoid timeouts and reduce memory requirements. As an added benefit, each request completes fairly quickly, which means it’s also quick to retry if an error should occur. And finally, a multithreaded application may be able to process one or more pages of results while it retrieves the next, speeding execution. These advantages have led developers at Google to adopt pagination as a best practice. Look for it in our APIs when you need to exchange large amounts of data, and consider adding it to your own services.
If you have questions about our services or APIs, or if you want to see what other developers are doing with Google Calendar, check the discussions and documentation in the Google Apps Calendar API forum.
Our newest set of APIs - Tasks, Calendar v3, Google+ to name a few - are supported by the Google APIs Discovery Service. The Google APIs Discovery service offers an interface that allows developers to programmatically get API metadata such as:
The APIs Discovery Service is especially useful when building developer tools, as you can use it to automatically generate certain features. For instance we are using the APIs Discovery Service in our client libraries and in our APIs Explorer but also to generate some of our online API reference.
Because the APIs Discovery Service is itself an API, you can use features such as partial response which is a way to get only the information you need. Let’s look at some of the useful information that is available using the APIs Discovery Service and the partial response feature.
You can get the list of all the APIs that are supported by the discovery service by sending a GET request to the following endpoint:
GET
https://www.googleapis.com/discovery/v1/apis?fields=items(title,discoveryLink)
Which will return a JSON feed that looks like this:
{ "items": [ … { "title": "Google+ API", "discoveryLink": "./apis/plus/v1/rest" }, { "title": "Tasks API", "discoveryLink": "./apis/tasks/v1/rest" }, { "title": "Calendar API", "discoveryLink": "./apis/calendar/v3/rest" }, … ] }
Using the discoveryLink attribute in the resources part of the feed above you can access the discovery document of each API. This is where a lot of useful information about the API can be accessed.
discoveryLink
Using the API-specific endpoint you can easily get the OAuth 2.0 scopes available for that API. For example, here is how to get the scopes of the Google Tasks API:
https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=auth(oauth2(scopes))
This method returns the JSON output shown below, which indicates that https://www.googleapis.com/auth/tasks and https://www.googleapis.com/auth/tasks.readonly are the two scopes associated with the Tasks API.
https://www.googleapis.com/auth/tasks
https://www.googleapis.com/auth/tasks.readonly
{ "auth": { "oauth2": { "scopes": { "https://www.googleapis.com/auth/tasks": { "description": "Manage your tasks" }, "https://www.googleapis.com/auth/tasks.readonly": { "description": "View your tasks" } } } } }
Using requests of this type you could detect which APIs do not support OAuth 2.0. For example, the Translate API does not support OAuth 2.0, as it does not provide access to OAuth protected resources such as user data. Because of this, a GET request to the following endpoint:
https://www.googleapis.com/discovery/v1/apis/translate/v2/rest?fields=auth(oauth2(scopes))
Returns:
{}
Using the API-specific endpoints again, you can get the lists of operations and API endpoints, along with the scopes required to perform those operations. Here is an example querying that information for the Google Tasks API:
https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=resources/*/methods(*(path,scopes,httpMethod))
Which returns:
{ "resources": { "tasklists": { "methods": { "get": { "path": "users/@me/lists/{tasklist}", "httpMethod": "GET", "scopes": [ "https://www.googleapis.com/auth/tasks", "https://www.googleapis.com/auth/tasks.readonly" ] }, "insert": { "path": "users/@me/lists", "httpMethod": "POST", "scopes": [ "https://www.googleapis.com/auth/tasks" ] }, … } }, "tasks": { … } } }
This tells you that to perform a POST request to the users/@me/lists endpoint (to insert a new task) you need to have been authorized with the scope https://www.googleapis.com/auth/tasks and that to be able to do a GET request to the users/@me/lists/{tasklist} endpoint you need to have been authorized with either of the two Google Tasks scopes.
POST
users/@me/lists
users/@me/lists/{tasklist}
You could use this to do some automatic discovery of the scopes you need to authorize to perform all the operations that your applications does.
You could also use this information to detect which operations and which endpoints you can access given a specific authorization token ( OAuth 2.0, OAuth 1.0 or Authsub token). First, use either the Authsub Token Info service or the OAuth 2.0 Token Info Service to determine which scopes your token has access to (see below); and then deduct from the feed above which endpoints and operations requires access to these scopes.
[Access Token] -----(Token Info)----> [Scopes] -----(APIs Discovery)----> [Operations/API Endpoints]
Example of using the OAuth 2.0 Token Info service:
Request:
GET /oauth2/v1/tokeninfo?access_token= HTTP/1.1 Host: www.googleapis.com
Response:
HTTP/1.1 200 OK Content-Type: application/json; charset=UTF-8 … { "issued_to": "1234567890.apps.googleusercontent.com", "audience": "1234567890.apps.googleusercontent.com", "scope": "https://www.google.com/m8/feeds/ https://www.google.com/calendar/feeds/", "expires_in": 1038 }
There is a lot more you can do with the APIs Discovery Service so I invite you to have a deeper look at the documentation to find out more.
Two months ago we announced that a few of us from the Google Apps Developer Relations team would be going around EMEA to meet with developers and talk about Google Apps technologies. We have met great developers from Germany, France, Russia, Czech Republic, Egypt, Switzerland, Israel, and Spain during Google Developer Days, hackathons, developer conferences and GTUG meetings.
This year we are continuing the tour with a series of Google Apps Script hackathons taking place in Vienna, Milan, Madrid, Munich and Dublin over the next few months. These hackathons provide a fun and hands-on way to learn about Google Apps Script and a good opportunity to give us your feedback on this technology.
For more information about the tour and to register for these events, please visit the Google Apps EMEA Developer Tour website.
We plan to organize many other Google Apps events close to you in the near future. Look for updates on the Google Apps EMEA Developer Tour website or keep an eye out for further announcements on this blog.
