Force Copy and Paste of the text only G-Sheet

Question

I created a document in google sheet, which uses conditional formatting. This document is shared with other people and each time they copy and paste from one cell to another, they also copy the conditional formatting of the cell of origin. Is it possible to allow the copy and paste of the text only for that document, without formatting? I found the solution to run the script every night that reorders the conditional formatting. Other solutions to avoid this? Thank you

Educate users to use Control+Shift+V (paste without formatting)? Otherwise it is probably possible to use OnEdit() to 'reset' what has just been pasted to ensure it is formatted as required. — P Burke
Hi, it would be nice, but it's not so easy to educate them. How can I perform a shortcut from the script? How can I identify that in the onEdit () function a shortcut was made and not a text input from the keyboard? Thank you — JoeJoe
I'm not sure what you mean by "perform a shortcut"? OnEdit() will trigger if the content of a cell is changed, either by typing or pasting (control-V). So my idea was to use OnEdit() to check if the conditional formatting was not as it should be, and fix it. And it sounds like you already have some code for the fix-it part; you just need to modify it to fix individual cells rather than the whole spreadsheet. — P Burke

Cooper Cooper · Accepted Answer · 2019-01-10T20:49:43

I don't know if this is a workable solution solution but I created conditional format rule for a sheet which governs the entire sheet. For column 1, I created some rules that control background color based upon the numeric values. For the entire rest of the sheet I set the conditional format background color to '#ffffff' which is my default color. So that when you copy from column 1 to any other column the conditional format rule that sets the background color to '#ffffff' governs the paste.

function customFormulaRule1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rules=[];
  var range=sheet.getRange(1,2,sheet.getMaxRows(),sheet.getMaxColumns());
  var rule=SpreadsheetApp.newConditionalFormatRule()
  .whenNumberBetween(1,10)
  .setBackground('#ffffff')
  .setRanges([range])
  .build();
  rules.push(rule);
  range = sheet.getRange("A1:A10");
  var rA=[{n:1,bg:"#b7e1cd"},{n:2,bg:"#fce8b2"},{n:3,bg:"#f4c7c3"}];
  for(var i=0;i<rA.length;i++){
    var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberEqualTo(rA[i].n)
    .setBackground(rA[i].bg)
    .setRanges([range])
    .build();
    rules.push(rule);
  }
  sheet.setConditionalFormatRules(rules);
}

In the following image I show what happens when you copy six cells from column A to column B.

Force Copy and Paste of the text only G-Sheet

1 Answers