CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Sylvain Dufeu
Sylvain Dufeu

Reputation: 3

How to activate in any G Sheets cell a macro that will copy and paste value in the next one?

I am looking for a simple way with G Script to copy and paste value of any cell that is selected when the macro is launched - or even better, to copy and paste value of a cell defined by its position compared to the selected one when the macro is launched.

Just to make it a bit more explicit, here is my full need: I have two columns in a spreadsheet, one with tick boxes ("TRUE" or "FALSE") and I would like a macro to transform the formula in the next column same line into hard value (copy and paste value), whenever the box from the first column is ticked ("FALSE" to "TRUE").

I was trying sth like this until now, in order 1) to select current cell 2) go to next one on the right 3) copy and paste value from it:

function PasteValue() {
  var spreadsheet = SpreadsheetApp.getActive();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.RIGHT).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

This is probably full nonsense.

Upvotes: 0

Views: 228

Answers (1)

doubleunary
doubleunary

Reputation: 19075

You can do that with an onEdit(e) simple trigger, like this:

function onEdit(e) {
  const checkboxColumn = 'Sheet1!C2:C';
  const columnOffset = 1;
  const checkboxes = SpreadsheetApp.getActive().getRange(checkboxColumn);
  if (e.value !== 'TRUE'
    || e.range.columnStart !== checkboxes.getColumn()
    || e.range.rowEnd < checkboxes.getRow()
    || e.range.rowStart > checkboxes.getLastRow()
    || e.range.getSheet().getSheetId() !== checkboxes.getSheet().getSheetId()) {
    return;
  }
  const cell = e.range.offset(0, columnOffset);
  cell.setValue(cell.getValue());
}

Upvotes: 1

Related Questions