CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yaniv Aflalo
Yaniv Aflalo

Reputation: 249

Google spreadsheet - treat "Ctrl + V" as "Ctrl + Shift + V"

Is there a way to define for a spreadsheet that Ctrl + V (regular paste) will be treated as Ctrl + Shift + V (paste values only)? Not only for myself, but for any user that can edit.

Reason: The spreadsheet in question is filled with data validation, formatting and conditional formatting settings. Users use Ctrl + V alot in this spreadsheet, so these settings are constantly being tampered with

Upvotes: 0

Views: 1847

Answers (2)

Yaniv Aflalo
Yaniv Aflalo

Reputation: 249

I am happy to update that I have achieved what I tried to achieve as follows:

  1. Create a copy of the template spreadsheet, to serve solely as a design setting template.
  2. Copy the relevant template sheet from the design setting template Ss into the destination spreadsheet (while keeping it hidden). This is acomplished using copyTo of class Sheet.
  3. Copy format, conditional formatting and data validation from template sheet to destination sheet. This is acomplished using copyTo of class Range.
  4. Deleting the tempalte (hidden) sheet from the destination spreadsheet.

Note: I included this function inside a library, as this should work in hundreds of spreadsheet copies.

**

The fucntion

**:

function restoreRangeSettings(e) {
// retrieves data validation, conditional formatting and formatting settings from template
  var editedSs = e.source;
  var editedRange = editedSs.getActiveRange();
  var editedSh = editedRange.getSheet().getName();

  var templateSs = SpreadsheetApp.openById('10zVclYTCEOEwskUID4vxSPxKR_tb3RaR76eG7TfxLUE');
  var templateSh = templateSs.getSheetByName(editedSh);
  var copiedSh = templateSh.copyTo(editedSs).hideSheet();
  var templateRange = editedSs.getSheetByName(copiedSh.getName()).getRange(editedRange.getA1Notation());

  templateRange.copyTo(editedRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  templateRange.copyTo(editedRange, SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);
  templateRange.copyTo(editedRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);

  editedSs.deleteSheet(copiedSh);
}

I relied heavily on the answer provided here.

ziganotschka, Thanks for your assistance!

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26836

There is no direct way to achieve it, but as a workaround you can attach to the spreadsheet an Apps Script with the following functionality:

  • The script contains an onEdit() function that fires automatically every time an edit to the sheet has been made (e.g. pasting)
  • It detects the active range that has been edited
  • It overwrites the contents of the active range with values only

Sample:

function onEdit(){
  var spreadsheet = SpreadsheetApp.getActive();
  var activeRange=spreadsheet.getActiveRange();
  activeRange.setValues(activeRange.getValues());
}

UPDATE

If you want to paste onEdit the formatting, conditional formatting and data validation for the edited range from a template - you can use the method copyTo(destination, copyPasteType, transposed) spcifying the copyPasteType accordingly.

Sample:

function onEdit(){
  var spreadsheet = SpreadsheetApp.getActive();
  var activeRange=spreadsheet.getActiveRange();
  var A1Notation=activeRange.getA1Notation();
  var templateRange=SpreadsheetApp.openById('ID OF THE TEMPLATE SPREADSHEET').getSheetByName('NAME OF THE TEMPLATE SHEET').getRange(A1Notation);
  templateRange.copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT);
  templateRange.copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING);
  templateRange.copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION);
}

Another option:

Use the method copyFormatToRange(sheet, column, columnEnd, row, rowEnd):

function onEdit(){
  var spreadsheet = SpreadsheetApp.getActive();
  var activeRange=spreadsheet.getActiveRange();
  var A1Notation=activeRange.getA1Notation();
var templateRange=SpreadsheetApp.openById('ID OF THE TEMPLATE SPREADSHEET').getSheetByName('NAME OF THE TEMPLATE SHEET').getRange(A1Notation);
templateRange.copyFormatToRange(spreadsheet.getActiveSheet(), activeRange.getColumn(), activeRange.getColumn(), activeRange.getRow(), activeRange.getRow());
}

Upvotes: 1

Related Questions