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();
}
Instead of getFormulas and setFormulas, use copyTo
method:
range.copyTo(newRange);
This will have the same effect as if you selected the last row and copied it below. In particular, it will also copy down any values that were entered directly into cells. If you prefer such values to not be copied, use getFormulas/setFormulas combination on newRange:
range.copyTo(newRange);
newRange.setFormulas(newRange.getFormulas());
The effect of the last line: no change of formulas, but the cells that didn't contain a formula will become empty.
R1C1 approach
You can also try replacing the last three rows of addRow
with
var formulas = range.getFormulasR1C1();
var newRange = sh.getRange(lRow+1,1,1,lCol);
newRange.setFormulasR1C1(formulas);
This will make sure the references in formulas are remapped as if you copy-pasted them. However, this has a major limitation: setFormulasR1C1 doesn't work correctly with empty string as its argument, which will result in #ERROR! messages in the cells that should be left blank.
The reason R1C1 works differently is that getFormulasR1C1
retrieves formulas in a relative form: for example, a reference from A3 to D2 becomes R[-1]C[3]
, meaning "one row up, three columns to the right". When the formula is set to, for example, E4 with setFormulasR1C1
, the cell reference will appear as H3.
Best Answer
At this time it's not possible to add a drop-down to an inserted image. The alternatives are to open an HTML dialog, a side-bar or a custom menu. For details, see the following official guides: