CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheets
doebtown
doebtown

Reputation: 563

Paste data validation only using Google Apps Script

This one should be an easy yes or no. Is it possible to paste data validation only with Google Apps Script?

What I want to do is have the code copy the data validation from the row above the active cell, then paste the data validation into the row of the active cell.

I tried copyTo:

function updateFormat() {
  var rowNumber = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection().getRow();
  var rowAbove = rowNumber -1 ;
  var targetRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(rowNumber, 1, 1, 36);      
  var templateRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(rowAbove, 1, 1, 36);

  templateRange.copyTo(targetRange);
}

but--obviously--that copied the data validation and the contents of the row above, which is not the goal.

Any ideas?

Thanks in advance!

Upvotes: 1

Views: 6414

Answers (5)

Wicket
Wicket

Reputation: 38219

There is a variant of copyTo that paste only the data validations:

copyTo(destination, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION)

On your specific case, instead of

templateRange.copyTo(targetRange);

use 

templateRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION);

Ref. range#copytodestination-copypastetype-transposed

Upvotes: 1

Enrique Umaran
Enrique Umaran

Reputation: 125

Just for the record, I think that there is a new validation class to solve these issues.

Upvotes: 1

luisplis
luisplis

Reputation: 1

/**
 * Copia en la fila nueva el formato y validaciones de la fila maestra, es decir, 
 * la cabecera si index = 1 o la que prefiramos, si no expecificamos index, cuando 
 * insertamos al final coge de maestra la anterior y al principo coge la siguiente.
 * @example function onEdit() { updateRules(); }
 **/
function sheetRowRules(index)
{
  var spread = SpreadsheetApp.getActive();
  var sheet  = SpreadsheetApp.getActiveSheet();
  var row   = sheet.getActiveCell().getRowIndex();
  var cols  = sheet.getMaxColumns();

  if (index)
    var i = index;
  else if (row > 2)
    var i = row-1; // anterior
  else
    var i = row+1; // siguiente a la cabecera
  var rg     = sheet.getRange(i, 1, 1, cols);
  var rango  = sheet.getRange(row, 1, 1, cols);

  // Copia el formato del anterior registro al actual y fórmulas de celdas para conservar validaciones de datos
  rg.copyTo(rango, {formatOnly:true});
  var formulas = rg.getFormulasR1C1().toString().split(",");

  //rango.setFormulasR1C1(formulas); // borra los valores, por eso así:
  for (var n = 0; n < formulas.length; i++)
  {
    if (formulas[n].length)
    {
      var col = n+1;
      var rango  = sheet.getRange(row, col, 1, 1);
      rango.setFormulaR1C1(formulas[n]);
    }
  }
  spread.toast("Formatos y fórmulas del replicados de "+i+" al registro "+row);
}

Upvotes: -1

mhawksey
mhawksey

Reputation: 2053

Adding the optArgument {formatOnly:true} works (See Ref). So

templateRange.copyTo(targetRange);

becomes

templateRange.copyTo(targetRange, {formatOnly:true});

I've refactored your code slightly and tested using:

function updateFormat() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowNumber = sheet.getActiveSelection().getRow();
  var rowAbove = rowNumber -1 ;
  var maxCols = sheet.getMaxColumns();
  var rangeToCopy = sheet.getRange(rowAbove, 1, 1, maxCols);
  rangeToCopy.copyTo(sheet.getRange(rowNumber, 1, 1, maxCols), {formatOnly:true});
}

Upvotes: 4

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

No.

But as a workaround, you could read all other values, formulas and formatting of your targetRange before you copy the template over it, then restore them using their specific formulas, e.g. setFormula, setValue and so on. Basically leaving only the data validation from the template.

Upvotes: 0

Related Questions