Google app script setValues() runs perfectly until it reaches a data validation error-CodePudding

This works perfectly if none of the data-validated cells raise an error on the sheet being written to. However, once the script reaches a cell where the copied value doesn't follow the data validation rules, it returns an error and stops executing. How can I ignore this error, continue to write into the cell, and continue running the script?


function addNew() {
  Week = "2022-01-03"
  x = 4
  y = 17

  for (var i=0; i<21; i  ) 
  {
    var readRange = SpreadsheetApp.getActive().getRange(`Versionality!I${x}:K${y}`)
    var readValues = readRange.getValues();
    var writeRange = SpreadsheetApp.openById("------");

    var writeValues = writeRange.getRange(`${Week}!H${x}:J${y}`)
    writeValues.setValues(readValues);

    x  = 17
    y  = 17
  }
}

CodePudding user response：

Let me propose this. You are doing a lot of getValues()/setValues() which can cause a performance issue. What I like to do is use only one getValues() and then we can extract what we need from the full array and write our portions of it as needed.

Also you were repeatedly opening the same spreadsheet by id.

I didn't test it because your date structure would be too hard to set up. But I'm pretty confident it will work.

function addNew() {
  let Week = "2022-01-03";
  let x = 3;  // array index is 1 less than row
  let y = 17;

  let spread = SpreadsheetApp.getActiveSpreadsheet();
  let readSheet = spread.getSheetByName("Versionality");
  let readValues = readSheet.getDataRange().getValues();
  let writeSheet = SpreadsheetApp.openById("------").getSheetByName(Week);

  for (let i=0; i<21; i  )  {
    let subArray = [];
    let j=x;
    while( j < y ) {
      subArray.push(readValues[j].slice(7,10)); // columns I to K
      j  ;
    }

    writeSheet.getRange(x 1,8,14,3).setValues(subArray);  // columns H to J 

    x  = 17;
    y  = 17;
  }
}

CodePudding user response：

Here is an example of how you can “disable/enable” data validation to copy data:

Sample code:

function myFunction(){
  // IMPORTANT: Make sure source and target dimensions are the same.

  var sourceRange = SpreadsheetApp.getActive().getRange("source!B11:D11"); //Get source range of cells to be copied
  var sourceRangeDVs = sourceRange.getDataValidations(); // cache the data validations currently applied to the sourceRange
  sourceRange.clearDataValidations(); //clear validations on the source range before getting source values
  var sourceValues = rangeSource.getValues(); //getting source values

  //Next, get target range where the data will be set. 
  //Note: if you have to open a new file and you are handling several ranges to be copied in a loop, 
  //    you may want to cache the target file on a variable to avoid opening every iteration of the loop
  var targetRange = SpreadsheetApp.getActive().getRange("target!E6:G6"); //Get target range of cells for the values to be set
  targetRange.setValues(sourceValues); //Set values copied from source in the target range

  sourceRange.setDataValidations(sourceRangeDVs); //Set Data Validations cached back to source range
}

Please note that the spreadsheet names and ranges set on the sample code above are merely to exemplify, please modify it accordingly.