Accept multiple inputs from user and add to certain cells in a new row

Viewed 2868

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.

1 Answers
Related