CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
3yed
3yed

Reputation: 13

Auto insert row above, based on single cell value

I am trying to auto-insert a row above the current top row (row 2) based on a cell's value.

spreadsheet picture

I cannot figure out the variables I need to put into the script editor.

I tried googling for help and modified other peoples script to see if I could do it, and the answer is no, no I do not possess the knowledge/skill.

function conditionalNewRow() {
  var ss = SpreadsheetApp.getActive().getSheetByName('NEW INV');
  var sh = ss.getActiveSheet();
  var headerRow = 1;
  var firstRow = headerRow + 1;
  var range = sh.getRange("A2"); // Get range of row 2.
  var futureRange = sh.getRange("A3"); // Get range of row 3.
  var numCols = range.getNumColumns(); // Get the number of columns for row 2.
  var contentVal = false;

  for (i = 1; i <= numCols; i++) {
    var currentValue = range.getCell(1,i).getValue();
    if (currentValue == "NO"){
      contentVal = true;
      break;
    }
  }  
  if (contentVal == true) {
    sh.insertRowBefore(firstRow); // Insert an empty row into row 2.       
    futureRange.copyTo(sh.getRange(firstRow, 1, firstRow, numCols), {contentsOnly:false}); // Copy row 3 to row 2.
  }
}

All I want is a blank row above the previous row when a cell meets certain criteria;

e.g. Cell A2 contains any of the following; YES, NO, LOADED, UNLOADED

If it contains any of those values, it will auto-insert a row above.

Upvotes: 1

Views: 3481

Answers (1)

Dustin Michels
Dustin Michels

Reputation: 3226

I am a little unclear on your intent and what issues you are having.

A few things I see:

1) Since your range is a single cell, var numCols = range.getNumColumns(); should always return 1, so your loop, for (i = 1; i <= numCols; i++), should run exactly once. It feels like there is a lot of redundant code here.

To get the value of cell A2, you could just write:

var range = sh.getRange("A2");
var currentValue = range.getValue();

2) From the if statements, it looks like you only want to add a new row if the value of A2 is something other than "NO". Is that correct? (This is not what your question states). If so, I think you're pretty close with the sh.insertRowBefore(firstRow); statement, it just might be getting lost in some convoluted logic.

3) Do you really want to copy everything from row 3 into row 2? In the question you state "All I want is a blank row above the previous row when a cell meets certain criteria".

Perhaps something like this is closer to what you want?

function conditionalNewRow() {
  var ss = SpreadsheetApp.getActive().getSheetByName("NEW INV");
  var sh = ss.getActiveSheet();

  var range = sh.getRange("A2"); // Get range of row 2.
  var currentValue = range.getValue();

  if (currentValue !== "NO") {
    sh.insertRowBefore(firstRow); // Insert an empty row into row 2.
  }
}

Edit

From your comment, sounds like you want to insert a new row whenever A2 is edited to one of the selectable values. You may want to check out simple triggers, such as onEdit, which runs whenever a cell is edited. For instance, something like this:

/**
 * The event handler triggered when editing the spreadsheet.
 * @param {Event} e The onEdit event.
 */
function onEdit(e) {
  // get sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName("NEW INV");

  // Get the edited range
  var editedRange = e.range;

  // check if cell A2 was edited
  if (editedRange.getA1Notation() === "A2") {
    // check if value is a desired value
    if (editedRange.getValue() === "YES" || "NO" || "LOADED" || "UNLOADED") {
      // if yes to both, insert new row
      sheet.insertRowBefore(2);
    }
  }
}

Upvotes: 1

Related Questions