CODEWITHSUNDEEP
google-sheets
BMcinnis
BMcinnis

Reputation: 1

Script to copy formulas when inserting row at the top - Google Sheets

I have a call log that has multiple drop-downs and formulas for making the data entry easier for the people using it. The problem is that when I insert a row (in row 2 under header) the data validation remains but the formulas don't copy. I have tried several scripts but none of them seem to work. I have about 10 columns with formulas in them.

Upvotes: 0

Views: 541

Answers (1)

Jacques-Guzel Heron
Jacques-Guzel Heron

Reputation: 2598

After reading your question and studying your example, I assume the following:

  • You want to add a new row after the headers.
  • You desire to keep the formulas and data validation rules in the old rows.
  • You wish to copy the formulas of the old rows into the new one.

If my assumptions are correct, you can use the following example to fulfill your requests:

CODE

function addRowAtTop() {
  var sheet = SpreadsheetApp.openById(
    '{SPREADSHEET ID}').getSheetByName(
    'PNC Calls');

  sheet.insertRowAfter(1);

  for (var i = 1; i <= 34; i++) {
    var cellFormula = sheet.getRange(3, i).getFormula();
    if (cellFormula != '') {
      sheet.getRange(2, i).setFormula(cellFormula);
    }
  }
}

BEHAVIOUR

The previous code will add a row at the top, and after that it will check each cell of the old first row. If it encounters a formula, it will copy it to the new first row.

ALLUSIONS

Please take this as one of the possible solutions to your issue, and don't hesitate to write me back with any additional doubts or requests to further clarifications.

Upvotes: 1

Related Questions