flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons)
Often, people really like the workflows enabled by Google Forms but they’d like it to go one step farther . . . like adding up two submitted items, or running a particular function/formula against the submitted data. You can certainly go in there and manually enter formulas or drag down to apply them to additional cells but that sucks and if you’re automatically displaying this data live somewhere it’s an impediment to a solid workflow. Most importantly, it sets a human to do something that a machine ought to be doing- that is the path to Skynet assuming control. We can and must resist! Subjugate the machines whenever possible!
The script below is broken into three parts and shows two different ways to set the value for columns based on data entered in a form.
Part 1 – This is pretty much default information that lets the script know which spreadsheet and which page it’s working with. The getLastRow() is really handy for applying this to data as it is entered.
This example form writes data to columns A – K.
In example one, we do the math internally and just spit the result into the cell. That’s handy for lots of stuff and can be simpler/more robust than writing a formula.
In example two, we’re writing a formula/function to the cell value. In this case I found it to be easier than trying to merge two arrays and then do some sort of count based on two criteria. It makes for a handy example of playing to your strengths. If I was better in javascript, I might have been able to write something faster than figuring out the Google Functions route but either way works.
function setFormulas() { //Part 1 - generic stuff you'd do for most any google sheets script var ss = SpreadsheetApp.getActiveSpreadsheet(); //gets the spreadsheet you attached the script to var sheet = ss.getActiveSheet(); //gets whatever sheet is in the front var lastRow = ss.getLastRow(); //gets the last row of entered data //Part 2 - Example 1 page count setting pure value without a function var startPage = ss.getRange('H'+lastRow).getValue(); //gets the value from the last row of column H var endPage = ss.getRange('J'+lastRow).getValue(); //same thing but w column J var readPages = endPage-startPage; //does a little math ss.getRange('L'+lastRow).setValue(readPages); //sets the value of last row of column L to the value of the variable we set with readPages //Part 3 - Example 2 helper count setting the value of the cell to be formula ss.getRange('M'+lastRow).setValue('=COUNTIFS($G$2:G'+lastRow+',$N$2,$C$2:C'+lastRow+',C'+lastRow+')'); }
The pattern for setting the value of a particular cell for the last row of data will be broadly applicable and even if you don’t know javascript at all, you can use this. Really.
I’ve tried to simplify it below with an example function that would add columns A, B, C to column D. The row is determined by getLastRow() so it can change with every form submission. You could copy/paste this and just change the yourColumn and yourFunction variables to make it do what you want.
function setFormulas() { var ss = SpreadsheetApp.getActiveSpreadsheet();//gets the spreadsheet you attached the script to var sheet = ss.getActiveSheet();//gets whatever sheet is in the front var lastRow = ss.getLastRow();//gets the last row of entered data var yourColumn = "D";//set your column var yourFunction = "=SUM('A'+lastRow ':' 'C'+lastRow)";//whatever function or formula or data you want, just don't forget to use lastRow do do the row number ss.getRange(yourColumn+lastRow).setValue(yourFunction); }
Once you have it set, don’t forget to tie it to a trigger. The example below will run every time a form submission is recorded.
Holy script judo! That’s super handy, a whole new world for me. One of the things with forms is you always have to scroll down to scan new data… I can see a script that would use a second sheet and do something like a unix tail command line thing, show the 10 (or user defined) newest rows
Yep. Super easy to do by script. Setting this to run on an “on open” trigger would work.
I’ve been wandering in Google Scripts land for the last couple of months and it’s truly crazy what’s possible. It makes me a bit nervous (as it’s all reliant on Google) but it is javascript and it is API stuff so if nothing else I’m learning patterns that might be applicable elsewhere.
Thank you for this helpful article. Can we do something to achieve the following:
I have sheet with some employee records where I would like to have the ability to update their data points such as title or manager or other data points based on the employee code.
Is this doable ?
Best Regards,
Syed H
Sure. You could do it with Google Script or just a write a VLOOKUP function that references the code/title pairing.