0

I'm trying to create a google script for sheets wherein it will generate some rows automatically with information and at the end of the row (example column Z), there will be a button generated. This button, what it will do is add a blank/empty row below. They want it on a button basis because it is only sometimes that they will insert a row below(so not everytime).

I already made the script to generate rows with information only, but it doesn't include the button with a separate script at column Z, but is there a way to do this? or it's just plain impractical/madness like reinventing the wheel?

The higher ups are super lazy that they would want a a blank row below in 1 click instead of right click and insert row in 2 clicks.

Thank you.

1 Answer 1

2

I would insert check boxes in that column and use them instead of a button. Your script would need to get the index of the active row on edit, check the value of the check box for true, add the row below the active row index and then set the value of the check box back to false.

Edit

If you select the whole column and insert check boxes, check boxes will automatically be added to any inserted rows.

Here is an example:

https://docs.google.com/spreadsheets/d/1NmNb-DMgtA2BaQi9KlEUcZatDU9A4lDkQ9J2580Rgk4/edit?usp=sharing

You can have an editable version by making a copy of this example sheet.

Here is the code:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function onEdit(e) {
  Logger.log('e.value: ' + e.value);
  
  var range = e.range;
  var value = e.value;
  var colIndex = range.getColumn();
  var rowIndex = range.getRow();
  var lastCol = sheet.getLastColumn();
  
  Logger.log('lastCol,colIndex,rowIndex: ' + lastCol + ',' + colIndex + ',' + rowIndex);
  
  //here the last column is the column with the check boxes
  if(colIndex == lastCol && value == "TRUE"){
    Logger.log('true');
    sheet.insertRowAfter(rowIndex);
    sheet.getRange(rowIndex,colIndex).setValue(false);
  }
}

If you are wanting the checkbox only on rows you generated, you can use something like:

var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
sheet.getRange(rowIndex,colIndex).setDataValidation(rule).setValue(false);

Of course, in this context, rowIndex would be the index of the row you generated.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.