0

I'm currently stuck trying to figure this out. Basically, I'm trying to insert an amount of empty cells only when cells from A2:B2 return a value. I got it to work where if both cells are empty one empty cell gets moved in. But I need both cells to move down if the criteria is met.

{
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("A2:A").moveTo(sheet.getRange("A3:A"));
  sheet.getRange("A2").setValue();
}

function insertSecondCell() 
{
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("B2:B").moveTo(sheet.getRange("B3:B"));
  sheet.getRange("B2").setValue();
}

function onEdit()
{
  let values = currentsheet.getRange("A2").getValue();
  let secondvalues = currentsheet.getRange("B2").getValue();
  if((values && secondvalues) != "")
  {
    insertSecondCell();
    insertCell();
  }
}
1

Use Range.insertCells(), like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
* Inserts one blank cell under A1 and B1, pushing cells A2:B2 and below down.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  const sheet = e.range.getSheet();
  if (e.range.rowStart !== 2
    || e.range.columnStart > 2
    || !sheet.getName().match(/^(Sheet1|Sheet2|Sheet3)$/i)) {
    return;
  }
  const inputRange = sheet.getRange('A2:B2');
  const values = inputRange.getDisplayValues().flat();
  if (values.every(value => value.length)) {
    inputRange.insertCells(SpreadsheetApp.Dimension.ROWS);
  }
}

Note that the onEdit(e) function is a simple trigger that runs every time any value in the spreadsheet is edited. It makes sense to minimize the number of API calls in the code, and use the event object e whenever possible.

If you want to insert rows instead of cells, take a look at the autoInsertRowAtTopOfSheet_ script.

3
  • Awesome, thanks! So I added your code function onEdit(e) { let values = currentsheet.getRange("A2").getValue(); let secondvalues = currentsheet.getRange("B2").getValue(); if ((values && secondvalues) !== '') { e.range.getSheet().insertRowAfter(2); } But its not moving the values on A2 and B2 down, also wouldn't .insertRowAfter() insert a complete row? I'm just looking to move the values to a certain location if a criteria is met
    – Toko
    Oct 5 at 16:04
  • Edited the answer to meet your requirements. Modify the Sheet1|Sheet2|Sheet3 regex to specify which sheet(s) you want the function to insert cells. Please edit your question to make it clear that you want to insert cells rather than rows. Oct 5 at 19:22
  • It works! Thanks, I edited my original post.
    – Toko
    Oct 5 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.