How do I automatically copy formules from above row in Google Sheets?-CodePudding

I have made a simple user interface to get user data. It gets a few entries, puts it into the designated database and clears the form. That works fine. While columns 0 to 6 are user data, columns 7 to 11 are formulas using data from 0 to 6. I'm looking for a solution that the user does not have to put in data and then manually needs to copy the above formulas. I don't want to put in the formulas before hand, because I think I will have a problem since the script looks for the last row.

So in short: it needs to find the last row on the sheet, write the data in to a new row and copy formulas in column 7 to 11 from the row above it to the new row. Total noob here, does this make sense?

function submitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Form");
  var datasheet = ss.getSheetByName("Database");

  var values = [[formSS.getRange("d3").getValue(),
                formSS.getRange("d5").getValue(),
                formSS.getRange("d7").getValue(),
                formSS.getRange("d9").getValue(),
                formSS.getRange("d11").getValue(),
                formSS.getRange("d13").getValue()]]
  
  datasheet.getRange(datasheet.getLastRow() 1,1,1,6).setValues(values)

  formSS.getRange("d3").clearContent();
  formSS.getRange("d5").clearContent();
  formSS.getRange("d7").clearContent();
  formSS.getRange("d9").clearContent();
  formSS.getRange("d11").clearContent();
  formSS.getRange("d13").clearContent();
}

CodePudding user response：

There are several options for solving your task:

Option 1: When generating an array of data for the row to be inserted, you can read the formulas from the previous row and add them to the array before inserting

...
  var values = [formSS.getRange("d3").getValue(),
                formSS.getRange("d5").getValue(),
                formSS.getRange("d7").getValue(),
                formSS.getRange("d9").getValue(),
                formSS.getRange("d11").getValue(),
                formSS.getRange("d13").getValue()],
      lastRow = datasheet.getLastRow(),
      formulas = datasheet.getRange(lastRow,7,1,5).getFormulasR1C1();
  
  datasheet.getRange(lastRow 1,1,1,11).setValues([[...values,...formulas[0]]])
...

Option 2. You can insert formulas directly in the script without reading them from the sheet

...
  var values = [[formSS.getRange("d3").getValue(),
                formSS.getRange("d5").getValue(),
                formSS.getRange("d7").getValue(),
                formSS.getRange("d9").getValue(),
                formSS.getRange("d11").getValue(),
                formSS.getRange("d13").getValue(),
                '=FormulaR1C1 for column 7',
                `=IMPORTXML(R1C2&R[0]C[-6]&"/","//div[@class='priceValue ']")`,
                '=FormulaR1C1 for column 9',
                '=FormulaR1C1 for column 10',
                '=FormulaR1C1 for column 11',]]
  
  datasheet.getRange(datasheet.getLastRow() 1,1,1,11).setValues(values)
...