I am trying to do the following in Google sheets using their script.
1) Accept multiple inputs from a user 2) Copy/paste the current last row to a new row underneath (the last row includes formulas I want to re-use) 3) Take the multiple inputs and fill out certain cells in the NEW last row.
For example, I have a column with labels: Product, Quantity, Cost, Tax, Ship, Buy. Product, Quantity, Cost are user inputs. Tax, Ship and Buy are formulas.
I want to prompt the user for "Product", "Quantity", "Cost" in one form.
Then I want to copy down the last row, and enter the results of "Product", "Quantity", "Cost" into the new last row.
Here is the script I have so far, I feel like I am so close:
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Evaluate')
.addItem('Add New Row', 'addNewRow')
.addItem('Compare', 'getData')
.addToUi();
}
var ss = SpreadsheetApp.getActive();
function addNewRow() {
var sh = ss.getActiveSheet(), lRow = sh.getLastRow();
var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
sh.insertRowsAfter(lRow, 1);
range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}
function getData() {
var ui = SpreadsheetApp.getUi();
var result = ui.prompt(
'Product',
ui.ButtonSet.OK_CANCEL);
var productName = result.getResponseText();
var sh = ss.getActiveSheet();
var lRow = sh.getLastRow(), lCol = sh.getLastColumn();
var productRange = sh.getRange(lRow,1).setValue(productName);
}
I can't figure out: 1) How to create a prompt that accepts multiple inputs 2) How to place that in cells based on the column title (seems more efficient that way)
Thank you.