Here how to do that with a script.
Code
function myFind(findRange) {
var output = [];
var find = ["Vault", "Drive", "Apps"];
for(var i=0, iLen=findRange.length; i<iLen; i++) {
for(var j=0, jLen=find.length; j<jLen; j++) {
if(findRange[i][0].indexOf(find[j]) !== -1) {
output.push([find[j]]);
break;
}
}
}
return output;
}
Explained
The range is searched by the key words in the find array. Once a word is found, the search is stopped and the next search will commence. You can add more key words in the var find array.
Note
The order of key words is very important. Better is to choose unique key words, like:
- Google Vault
- Google Drive
- Google Apps
All is of course dependent upon the data available.
Example
I've added the script (Tools>Script editor) and the ARRAYFORMULA
version of the solution Punchlinern provided in your example file.
I found that this works. Change the form submit button of the html file to:
<input onclick="formSubmit();go();" type="button" name="mysubmit" id="mysubmit" value="Add Row"/>
In the html file, add this:
<div id="div">waiting...</div>
<script>
function callback(whatToWrite) {
document.getElementById("div").innerHTML=whatToWrite;
}
function go() {
google.script.run.withSuccessHandler(callback).writeSomething();
}
</script>
In code.gs, add this:
function writeSomething() {
var ss = SpreadsheetApp.openById("SHEETIDHERE").getSheetByName("DATA")
var lastRow = ss.getLastRow();
var range = ss.getRange(lastRow, 1, 1, 2);
var Names = range.getValues();
return Names;
}
These changes have the desired result of updating a div with the newest last row each time a new row is inserted. This makes a nice way of showing users that their form submission is successful, and the form remains open to receive more data.
I worked this out with the answer found here: how to use google.script.run as if it was a function. I will update my example Sheet, in case this will be helpful to someone else.
UPDATE: If you are editing existing rows, then you need to move the call of the go() function to inside the formsubmit function. so that the newly updated row is shown in the confirmation div, and not just the last row.:
function formSubmit() {
google.script.run.getSireDamfromAutocomplete(document.forms[0]);
go();
}
Best Answer
A column of values is represented in Apps Script as
[['a'], ['b'], ['c']]
. A row is represented as[['a', 'b', 'c']]
. The following functions transform one to the other: