G Suite Developers Blog

Approval Workflow using Apps Script

April 17, 2012

Editor’s Note: This blog post is authored by Dito’s Steve Webster who is a Google Apps Script Top Contributor - Saurabh Gupta Ethics Disclosure Review WorkflowRecently a company who operates retail stores throughout a few states reached out to Dito. When their associates conduct business discussions with vendors or customers where monetary exchanges are involved, their Ethics Disclosure policy requires a log for each event, a supervisor approval, and committee review. The customer’s existing disclosure review process was cumbersome and time consuming. The employees would add a disclosure review request to a spreadsheet with 29 columns. The new review request would then be reviewed by their supervisor. The supervisor and the employee would go back and forth exchanging emails and making changes to a dense spreadsheet until an approval was granted. It was clear that the customer needed a workflow solution. They decided to hire Dito to build a workflow solution based on Google Apps Script. Workflow Solution based on Google Apps Script To make the process more user friendly and productive, Dito decided to build a user interface to collect ethics disclosure events, make updates, and automate the routing of email notifications. Writing a Google Apps Script to create a user interface (UI), enabled associates to interact with their contacts to select their supervisor’s email address and simplify the data collection with list boxes. The script sends approval emails with HTML form radio buttons, text box, approve/decline buttons, and a “Post” command to invoke other workflow scripts. Below are some of the main design points for this Approval Workflow script. 1. Disclosure Review WorkflowThe Disclosure Review workflow requires (a) Associates to fill out the Ethics Disclosure form. (b) Supervisor to either approve or decline the submission. (c) If supervisor approves, the Ethics Disclosure Committee is notified. (d) If supervisor declines, the associate is notified to make corrections. (e) After improving the submission, the workflow repeats itself.

2. Disclosure Review Request Form Dito developed a custom review request form. A form was developed using Google Apps Script’s UI Services. The form provides the ability to look up data to populate such things as a drop-down list box. This form allowed real-time email look-ups by using the Apps Script’s Contacts service. First efforts included per character look-ups in a list box, but since they have over 1,000 users, it was best to enter the first and/or last name of their supervisor before initiating the look-up (see code snippet below). var byName = ContactsApp.getContactsByName(searchKey); for (var i in byName) { var emailStr = byName[i].getPrimaryEmail(); // If there is no 'primary' email, try again for the next email if (emailStr == null) var emailStr = byName[i].getEmails()[0].getAddress(); // If emailStr is still null, try again by getting the next email if (emailStr == null) var emailStr = byName[i].getEmails()[1].getAddress(); } Another dynamic field was the “activity type”. Depending on the selection more form fields are displayed. For example, if the activity type is “Meals”, display a list box to select lunch or dinner.3. Approve or Reject directly in GmailWhen an associate submits his/her review request by using the custom form within a spreadsheet, their supervisor receives an email with easy-to-read HTML formatted results. The approval decision, as well as a comment field (e.g. decline reason), is made within the email. This is more productive and prevents unnecessary back and forth into the spreadsheet.

If the request is declined by the supervisor, the associate who submitted the review request receives an email and can review the details. The email also contains a “Continue” button which opens the form in a new browser tab. After corrections are submitted, the supervisor receives another email and the workflow repeats itself.

When approved, the Ethics Disclosure Committee is notified by sending a group email within the script.4. Saving Workflow HistorySince history records existed in their original spreadsheet form and they wanted to insert these records into the new work flow spreadsheet as a one-time task, an Apps Script was used to copy the data. Of course their columns did not match the new spreadsheet. By using a mapping approach and a “read once” and “write once” technique, the Apps Script quickly made the changes.function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Sheet 1'); var rowsWithData = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues() var sheet1Data = []; var sheet2Data = []; for (var i = 0; i < rowsWithData.length; i++) { switch (rowsWithData[i][4]) // This is the activity type { ... case "Gift": sheet1Data.push([rowsWithData[i][12], rowsWithData[i][13], rowsWithData[i][14]]); sheet2Data.push([rowsWithData[i][15]]); continue; ... default: continue; } } sheet.getRange(2, 6, sheet1Data.length, 3).setValues(sheet1Data); sheet.getRange(2, 12, sheet2Data.length, 1).setValues(sheet2Data); } Google Apps Script is very powerful and Dito uses it to build interesting solution for its customers. If you are using Google Apps then be sure to use Google Apps Script. You’ll be amazed with what you can build with it.

Steve Webster profile

Google Sites and Scripts expert from Dito specializing in training and application development. When not busy finding solutions to enhance customer capability in Google Apps, Steve shares examples of his work in the Google Apps Developer Blog.

Crowd Sourcing with Google Forms and Fusion Tables

April 13, 2012

Crowd sourcing has been growing substantially in popularity. More and more businesses and individuals are interested in gathering data from the general public for real-time data analysis and visualization. The concept is being adopted in several fields, including journalism, public health and safety, and business development. During this election year, for example, a journalist might be interested in learning what candidate his or her readers support, and the reasons why they support this candidate.Google Forms, Fusion Tables, and Apps Script make both data collection and analysis super simple! Using Google Forms, a journalist can quickly create an HTML form for readers to submit their opinions and feedback. Fusion Tables make data analysis easy with several cool data visualization options. Apps Script acts as the glue between Google Forms and Fusion Tables, enabling the Form to send data directly to Fusion Tables.Let’s take a look at how our journalist friend would use all these tools to collect her reader’s candidate preferences.Google FormsGoogle Forms provides a simple UI tool to develop forms perfect for collecting data from readers. Here’s an example of a simple form the journalist can create to get information from her readers:

Once the form has been created, it can be embedded directly into the journalist’s website or blog using the embeddable HTML code provided by Google Forms.Google Fusion TablesGoogle Fusion Tables makes data analysis simple with its visualization capabilities. Using Fusion Tables, the journalist can create maps and charts of the collected data with just a few clicks of the mouse!Using some fake data as an example, here’s a pie chart that can be created using Fusion Tables to show the the results of the survey:

With Fusion Tables, it’s also easy to filter data and create a pie chart visualization showing why people like Mitt Romney:

These visualizations can also be embedded in the journalist’s website or blog, as Fusion Tables provides embeddable HTML code for all its visualizations. Now, any time someone visits the webpage with the embedded visualization, they will see the current poll result!Apps ScriptFinally, Apps Script acts as the glue between the Google Form and the Fusion Table, since there is currently no direct way to send Google Form submissions to a Fusion Table. During a hack event last year, I took some time to write an Apps Script script that submits the form data to Fusion Tables. The script uses the onFormSubmit Apps Script functionality as described in this blog post. The Fusion Tables code is based on the code described in this blog post.To learn how to set up your own Google Form to collect data and save that data in a Fusion Table, please see these instructions.

Kathryn Hurley profile

Kathryn is a Developer Programs Engineer for Fusion Tables at Google. In this role, she helps spread the word about Fusion Tables by presenting at conferences and developer events. Kathryn received an MS in Web Science from the University of San Francisco. Prior work experience includes database management, web production, and research in mobile and peer-to-peer computing.

Introducing the Script Service

April 5, 2012

Triggers in Apps Script allow you to schedule your script’s execution in response to an event, at a specific time and date, or at given time intervals. Publishing scripts as services allows you to deploy your web apps with a click of a button. The new Script service lets you perform both of these tasks programmatically. With the Script service at your disposal, you can create smooth install flows, chain your scripts’ executions, generate summaries of current triggers, and even programmatically publish the scripts as services.Create smooth install flowsYou can now set up your triggers from a menu. Here is an example of how you would schedule function foo() to run one year from now via a custom menu in Google Spreadsheets:function onOpen() { SpreadsheetApp.getActive() .addMenu("Setup", [{name: "Create Trigger", functionName: "createTrigger"}]); } function createTrigger() { var now = new Date(); ScriptApp.newTrigger("foo") .timeBased() .atDate(parseInt(now.getFullYear())+1, now.getMonth(), now.getDate()) .create(); }Chain script executionsBy programmatically setting triggers, you are able to schedule a future script’s executions from the script that is currently running. Here is an example of a function that schedules another function to execute two hours after it completes:// this function is run to send the initial reminder and schedule a subsequent one function sendRequest() { GmailApp.sendEmail("class@example.edu", "Assignment #1 is due in 2 and a half hours!", "Your first assignment is due in 2 and a half hours!"); var now = new Date(); var later = new Date(now.getTime() + 2 * 60 * 60 * 1000); ScriptApp.newTrigger("sendReminder").timeBased().at(later).create(); } // this function will execute two hours later, sending another reminder function sendReminder() { GmailApp.sendEmail("class@example.edu", "Assignment #1 is due in 30 minutes!", "Your first assignment is due in half an hour!"); }Generate a summary of current triggersWith the ability to schedule triggers programmatically, it is important to keep track of the existing ones. The function below logs all the triggers that are associated with the current script. Alternatively, you could also write this summary to a spreadsheet or email it to yourself.function logMyTriggers() { var triggers = ScriptApp.getScriptTriggers(); for(i in triggers) { Logger.log("Trigger ID: " + triggers[i].getUniqueId() + "\nTrigger handler function: " + triggers[i].getHandlerFunction() + "\nTrigger type: " + triggers[i].getEventType() + "\n----------------------"); } } Publish scripts programmaticallyYou can now let your users publish your scripts through a Google Spreadsheets custom menu as a part of the script’s set up process. Below is an illustration of this process using an example of a simple note-taking web app. Once a user clicks the menu item, the script becomes published as a service and the URL is presented back to the user via a dialog box.function doGet(e) { var app = UiApp.createApplication(); createApp(app, false); return app; } function doPost(e) { var now = new Date(); MailApp.sendEmail(Session.getEffectiveUser(), "Notes taken at " + now, e.parameter.notes); var app = UiApp.getActiveApplication(); createApp(app, true); return app; } function createApp(app, isSubmitted) { app.setWidth(550) .setHeight(700); var vp = app.createVerticalPanel(); var title = app.createHTML("Enter your notes into the text area below." + "<br>Hit submit to email them to yourself!") .setStyleAttribute("fontSize", "25px"); var lbl = app.createLabel("Notes submitted!") .setStyleAttribute("fontSize", "17px"); var form = app.createFormPanel(); var notes = app.createTextArea() .setWidth("500px") .setHeight("500px") .setName("notes"); var sb = app.createSubmitButton("Submit"); vp.add(title) .add(notes) .add(sb); if (isSubmitted) vp.add(lbl); form.add(vp); return app.add(form); } function onInstall() { onOpen(); } function onOpen() { SpreadsheetApp.getActive() .addMenu("Setup", [{name: "Publish as a service.", functionName: "setup"}]); } function setup() { var url = ""; try { ScriptApp.getService() .enable(ScriptApp.getService().Restriction.MYSELF); url = ScriptApp.getService().getUrl(); Browser.msgBox("Your web app is now accessible at the following URL:\n" + url); } catch (e) { Browser.msgBox("Script authorization expired.\nPlease run it again."); ScriptApp.invalidateAuth(); } }The Script service provides you with the means of creating even more powerful applications and makes the development and deployment process smooth.For more information, please visit the Script service Reference Documentation and User Guide.

Anton Soradoi profile

Anton is a Developer Support Specialist at Google. He works closely with Apps Script developers to help them discover its full potential. Prior to Google, he worked as a Web Developer.

A First Attempt at Apps Script with Spreadsheets

March 30, 2012

The Apps Script team held a hackathon in Washington DC on March 7. Over 80 developers attended and we had some great demos at the end of the evening. One of the demos was from Rusty Mellinger, who explains his script in this blog post. If you missed the DC hackathon, sign up for our next one in Chicago on April 19. -Jan KleinertI was lucky enough to attend Google’s Apps Script Hackathon at their office in DC, recently, and got a chance to play with Apps Script. After a quick walk-through tutorial, Jan gave us a couple of hours to hack around with it.Scripts in Apps Script are written in JavaScript and stored, edited, and run on Google's servers, interfacing with a big list of included services. You can call the scripts from spreadsheets, Google Sites, or from hits to a generated URL.Roommate Payment Spreadsheet

My roommates and I keep a spreadsheet on Google Docs to track who owes what, but since we’re a house full of software engineers, it’s gotten pretty complicated. Each row records the details of a single transaction: who paid, the total, and what percentages of the payment are on behalf of which roommates. All these interpersonal debts are added up into the (J5:M8) matrix, cancelled out across the diagonal into (P5:S8) to get a single debt for each roommate pairing, and then those are totalled into the final "Shake Out", (F4:F7), which says whether you owe or are owed. Maybe Apps Script could make my life simpler here?Automatic EmailsFirst, I’m currently owed a fair amount of money, so I set up automated reminder emails to the roommates who are behind: // Send emails to everybody with their current status. function emailDebtors() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var results = ss.getRange( "G4:G7" ).getValues(); var emails = ss.getRange( "O3:R3" ).getValues(); var numUsers = 4; for(var i = 0; i != numUsers; i++) { var val = Math.round(results[i][0]); if (val > 0) { // This guy owes money in the shake-out. MailApp.sendEmail( emails[0][i], "You're a deadbeat!", "You owe $" + val + ". Please pay it!"); } } }This just pulls the current totals from the (G4:G7) "Shake Out", as well as their respective email addresses from (O3:R3). When this function is called, if any of them owe more than $0, they get a friendly reminder!Custom Menus

I could set that up to trigger daily or weekly, but it only really needs to happen when somebody needs to collect what they’re owed, so I’ve added it as an option to the sheet’s menu on start-up.function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ { name: "Email debtors", functionName: "emailDebtors"}]; ss.addMenu( "SkyCastle", menuEntries ); }Easy! Now when somebody wants to collect, they just click the “SkyCastle -> Email debtors” option and the appropriate reminder emails are sent out, from their own Gmail address.Historical Charting

I still had a couple of hours, and wanted to play with the UI and Google Charts services, so I decided to chart the “Shake Out” values over the history of the spreadsheet. The existing cells are hard-coded to operate on the total sums from the full sheet, so I had to re-implement the math to track it line-by-line. (This isn’t all bad, because I can use it to double check the existing arithmetic, which was sorely needed.)The basic sketch is as follows:var data = Charts.newDataTable() .addColumn(Charts.ColumnType.NUMBER, "Row"); for (var i = 0; i != 4; i++) { data.addColumn(Charts.ColumnType.NUMBER, names[i]); } for (var i = 0; i != NUMROWS; i++) { var row = Array(5); // … // Process the current line here, and compute the shake-out. // … data.addRow(row); } data.build();I’ve omitted the actual calculation, because it’s just a bunch of hacks specific to our spreadsheet formulas. Each row contains the row number, and the accumulated shake-out thus far, and gets added to the `data` table. I break out of the loop once I go off the end of my data and start hitting `NaNs`.To create the line chart and add it to a new UI window:var chart = Charts.newLineChart() .setDataTable(data) .setDimensions(700, 400) .setTitle("Debt History") .build(); var uiApp = UiApp.createApplication() .setWidth(700) .setHeight(400) .setTitle("Payment History"); uiApp.add(chart); ss.show(uiApp); return uiApp;After adding this function as another option in our custom `SkyCastle` menu and clicking it, we see a nice graph. (I’m almost always on the bottom, but that’s because I make the actual rent and utility payments.) The final entries are equal to the original "Shake Out" cells, so our old arithmetic seems correct, too.Lessons LearnedThe built-in debugger isn’t bad; use the `Select function` dropdown and click the bug icon. I also used Logger.log() liberally while trying to get things working right. (Go to `View -> Logs` in the Script Editor to view that output.)Apps Script seems to work well, overall, and hooks into a nice and expanding array of Google products and data sources. The GWT-backed UI service is a clever idea, though I barely had a chance to touch it.Thanks again to Jan and Google for hosting this Hackathon; I can’t wait for the next one!

Rusty Mellinger

Rusty Mellinger co-founded Illogic Inc, making heavy use of Google Apps and GWT.

Making PATCH requests from App Engine

March 29, 2012

PATCH requests allow you to perform partial updates on many of our REST APIs and in most cases can save bandwidth.If you have ever tried to do a PATCH request on an App Engine application, you probably realized that it is not possible and that the list of HTTP Methods allowed is whitelisted to the following methods only: GET, POST, HEAD, PUT and DELETE. Trying to perform a PATCH request raises the following Exception:java.net.ProtocolException: PATCH is not one of the supported http methods: [GET, POST, HEAD, PUT, DELETE]There is a workaround to this. Most of our APIs support the X-HTTP-Method-Override header. This header can be used in a POST request to “fake” other HTTP methods. Simply set the value of the X-HTTP-Method-Override header to the HTTP method you would like to actually perform.For example, to make a PATCH request to the Google Tasks API to update only the Notes field of a particular task you could use the following HTTP request:POST /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1 Host: www.googleapis.com X-HTTP-Method-Override: PATCH Authorization: Bearer Content-Type: application/json Content-Length: 31 {“Notes” : “Patch is working!”}Which would be equivalent to this HTTP Request, which is not supported on App Engine: PATCH /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1 Host: www.googleapis.com Authorization: Bearer Content-Type: application/json Content-Length: 31 {“Notes” : “Patch is working!”}For instance, in an App Engine Java environment you could construct and execute this request this way:URL url = new URL("https://www.googleapis.com/tasks/v1/" + "lists/@default/tasks/" + TASK_ID); HTTPRequest request = new HTTPRequest(url, HTTPMethod.POST); request.addHeader(new HTTPHeader("X-HTTP-Method-Override", "PATCH")); request.addHeader(new HTTPHeader("Authorization", "Bearer " + ACCESS_TOKEN)); request.setPayload("{\"Notes\" : \"Patch is working!\"}".getBytes()); URLFetchService fetchService = URLFetchServiceFactory.getURLFetchService(); HTTPResponse response = fetchService.fetch(request);This trick can also be used if your application server is behind a firewall, behind a proxy server or in any other environment where HTTP methods other than POST might not be allowed. In that case you could use the X-HTTP-Method-Override header the same way to workaround these limitations.You may also use our Google APIs Client library for Java or our Google APIs Client library for Python, both of which have support for PATCH requests and use the X-HTTP-Method-Override header when appropriate.

Nicolas Garnier profile | twitter | events

Nicolas joined Google’s Developer Relations in 2008. Since then he's worked on commerce oriented products such as Google Checkout and Google Base. Currently, he is working on Google Apps with a focus on the Google Calendar API, the Google Contacts API, and the Tasks API. Before joining Google, Nicolas worked at Airbus and at the French Space Agency where he built web applications for scientific researchers.

Integrating Google Docs with Salesforce.com using Apps Script

March 12, 2012

Editor's Note: Ferris Argyle is going to present Salesforce Workflow Automation with Google Spreadsheet and Apps Script at Cloudforce. Do not miss Ferris's talk - Saurabh Gupta As part of Google's Real Estate and Workplace Services (REWS) Green Team, the Healthy Materials program is charged with ensuring Google has the healthiest workplaces possible. We collect and review information for thousands of building materials to make sure that our offices are free of formaldehyde, heavy metals, PBDEs and other toxins that threaten human health and reduce our productivity. A Case for using Google Docs and Salesforce.com My team, as you might imagine, has a great deal of data to collect and manage. We recently implemented Salesforce .com to manage that data, as it can record attributes of an object in a dynamic way, is good at tracking correspondence activity and allows for robust reports on the data, among many other functions. We needed Saleforce.com to integrate with our processes in Google Apps. We wanted to continue collecting data using a Google Docs form but needed it integrated with Salesforce.com because we:

Liked the way the form looked and functioned

Wanted to retain continuity for our users, including keeping the same URL

Wanted a backup of submissions

And this is where Google Apps Script came to our rescue. We found that we could use Google Apps Script to create a new Case or Lead in Salesforce.com when a form is submitted through our Google Docs form. This allowed us to continue using our existing form and get our data directly and automatically into Salesforce.com. Google Docs + Apps Script + Salesforce.com = Integrated Goodness! Salesforce.com has two built-in options for capturing data online - Cases and Leads. Google Docs Forms can capture data for both of them. Set up your Case or Lead object with the desired fields in Salesforce.com. The next step is to generate the HTML for a form. You will use the IDs in the Salesforce.com-generated HTML when writing your Google Apps script. A) Getting the HTML in Salesforce.com: 1. Login to Salesforce.com and go to Your Name > Setup > Customize > Leads or Self-Service (for Cases) > Web-to-Lead or Web-to-Case 2. Make sure Web-to-Lead/Web-to-Case is enabled. Click on Edit (Leads) or Modify (Cases) and enable if it is not. 3. Click on the 'Create Web to Lead Form' button (for Leads) or the 'Generate the HTML' link (for Cases) 4. Select the fields you want to capture and click 'Generate'. Save the HTML in a text file. You can leave 'Return URL' blank

B) Setting up Google Apps Form/Spreadsheet: Create your form and spreadsheet (or open up the one you already have and want to keep using). This is very easy to do. Go to your Docs and click on 'Create' to open a new form. Use the form editor to add the desired fields to your form- they'll show up as column headings in the corresponding spreadsheet. When someone fills out your form, their answers will show up in the right columns under those headings. C) Writing the Google Apps Script: The script is set up to take the data in specified cells from the form/spreadsheet and send it into designated fields in your Salesforce.com instance (identified by the org id in the HTML generated above). For example, the form submitter's email is recorded through the form in one cell, and sent into the email field in either the Lead or Case object in Salesforce.com. 1. Create a new script (Tools > Script Manager > New). 2. Write the script below using the pertinent information from your Salesforce.com-generated code (shown further down). function SendtoSalesforce() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var row = sheet.getLastRow(); var firstname = sheet.getRange(row, 2).getValue(); var lastname = sheet.getRange(row, 3).getValue(); var email = sheet.getRange(row, 4).getValue(); var company = sheet.getRange(row, 5).getValue(); var custom = sheet.getRange(row, 6).getValue(); var resp = UrlFetchApp .fetch( 'https://www.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8', { method: 'post', payload: { 'orgid' : '00XXXXXXXX', 'first_name' : firstname, 'last_name' : lastname, 'email' : email, 'company' : company, '00YYYYYYYY' : custom, 'external' : '1' } }); Logger.log(resp.getContentText()); } Define your variables by directing the script to the correct cell (row, column number). Then in the payload section, match the field id from your Salesforce.com HTML (red) to the variable you defined (blue). For example, the email address of the submitter is defined as variable 'email', can be found in the 4th column of the last row submitted, and the id for that field in Salesforce.com is 'email'.

Note that any custom fields you've created will have an alpha-numeric id. 3. Save your script and do a test run. D) Wiring Script to a Form Submission. To send your data automatically into Salesforce.com, you need to set a trigger that will run the script every time a form is submitted. To do this, go to your script and click Resources>Current script's triggers. 1. Create a Trigger for your function so that it runs when a form is submitted.

2. Post the link to your form on your website, send it in an email, link to it on G+, etc. Get it out there! That's it! Now when someone submits a form, the information will come into your spreadsheet, and then immediately be sent into Salesforce.com. You can adjust your Salesforce.com settings to create tasks when the information comes in, send out an auto-response to the person filling out the form and set up rules for who is assigned as owner to the record. You'll also have the information backed up in your spreadsheet. This has been a great solution for our team, and we hope others find it useful as well!

Beth Sturgeon

Beth Sturgeon is a member of Google's Green Team in Mountain View, which makes sure that Google's offices are the healthiest, most sustainable workplaces around. Prior to Google, she had a past life as a wildlife researcher.

Sheetcaster: 3D in Apps Script

March 8, 2012

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 KleinertRecently, 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:

Cast a ray

Move along the ray until hitting a wall, calculate the distance to that wall

Draw a column whose height is inversely proportional to that distance

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:

Its (real-valued) x/y coordinates in the map plane

Its angle relative to some predefined direction

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:

Its origin is the camera's 2D coordinates in the map plane

Its direction is calculated off the camera's and the column index (the center column will have the exact same direction as the camera; the other columns' directions depend on the field of view parameter)

/* * 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:

Calculate the distance to the next vertical and horizontal borders

Move to the closest border

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:

Accumulate the length of runs (successions of same-sized columns)

Draw a gradient, from the background (wall and floor) to the wall, above (and below) the columns

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.