Google sheets script moving all rows starting from specific row number

Question

I'm trying to script a verification button to move all rows from a specific row number. They need to be moved down 1 row for historical log. Also a new row at the top (row 17) needs to be created with the same check boxes and todays date. Please refer to this link to the sheet

Bonus question: Is it also possible as the owner of the sheet, to have the moved down "logged rows" protected from changes with scripting?

I'm not sure where to start. I believe it's a simplified version of this

EDIT: I have realized that instead of copying multiple rows, I should "insert row".

I have now made some progress but it does not seem to work as I intend. The script should select row 17 and insert a row below this.

Here's my script:

function veriFy() {
  

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeToCopy = sheet.getRange('A17');
var row = sheet.getActiveCell().getRow();

sheet.setCurrentCell(rangeToCopy);


var selection = sheet.getSelection();
var currentCell = selection.getCurrentCell();


sheet.insertRowAfter(row);
rangeToCopy.copyTo(sheet.getRange(row + 1, 1));


}

It does not add the row when running, only select "A17". Though if "A17" is already selected, the script will in fact add the row successfully.

Any help would be greatly appreciated.

Answer 1

You want to

• insert a new row at Row 17
• move existing rows down a row
• Update the "new" Row 17 with today's date and the "Unverified" image
• update the "old" Row 17 with the "Verified" image.

---

function verifySheet() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetName = ss.getActiveSheet().getName()
  var sheet = ss.getSheetByName(sheetName)

  // insert row
  sheet.insertRowBefore(17);

  // insert date
  sheet.getRange('B17').setValue(new Date())

  // copy the UnVerified image from Cell A18 to Cell A17
  var imagesSheetname = "images"
  var imagesSheet = ss.getSheetByName(imagesSheetname)
  var rangeToCopy = imagesSheet.getRange("A1")
  rangeToCopy.copyTo(sheet.getRange("A17"))

  // copy the Verified image from Cell A19 to Cell A18
  var rangeToCopy = imagesSheet.getRange("A2")
  rangeToCopy.copyTo(sheet.getRange("A18"))
}

---

Notes

• insertRowBefore(17): inserts a new row at row 17, and automatically inserts blank checkboxes in Columns D-F
• sheet.getRange('B17').setValue(new Date()): inserts current date into Column B
• Created new sheet 'images" and saved copies of the Unverified and Verified images in Cells A1 and A2. This avoids any problem where the following row does not, for any reason, have a copy of the "Verified" image

---

SAMPLE-AFTER

---

SAMPLE-BEFORE