Google Apps Script Replacing Text

Question

I have a google sheet that in column A I need to replace text on a button push with NONE. The column has headers in a few different rows that need to remain the same. I wrote the following script:

function part() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Builder');
  sheet.getRange('A12').setValues([['NONE']])
  sheet.getRange('A15:A16').setValues([['NONE']])
  sheet.getRange('A19:A22').setValues([['NONE']])
  sheet.getRange('A25:A30').setValues([['NONE']])
  sheet.getRange('A33:A36').setValues([['NONE']])
  sheet.getRange('A39:A42').setValues([['NONE']])
  sheet.getRange('A45:A52').setValues([['NONE']])
  sheet.getRange('A55:A59').setValues([['NONE']])
  sheet.getRange('A62:A66').setValues([['NONE']])
}

I get the following error when I try to run the script: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 2.

I know it has to do with rows having more columns than other rows but not sure how to fix the code.

Cooper Cooper · Accepted Answer · 2019-10-17T17:53:01

Filling single column ranges with data

function part() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Builder');
  var rangeA=["A12","A15:A16","A19:A22","A25:A30","A33:A36","A39:A42","A45:A52","A55:A59","A62:A66"];
  rangeA.forEach(function(A1){
    var rg=sheet.getRange(A1);
    var vA=rg.getValues();
    vA.forEach(function(r){
      r[0]="NONE";
    })
    rg.setValues(vA); 
  })
}

This will do it for any size range:

function part() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Builder');
  var rangeA=["A12","A15:A16","A19:A22","A25:A30","A33:A36","A39:A42","A45:A52","A55:B59","A62:A66"];
  rangeA.forEach(function(A1){
    var rg=sheet.getRange(A1);
    var vA=rg.getValues();
    vA.forEach(function(r){
      for(var i=0;i<r.length;i++) {
        r[i]="NONE";
      }
    })    
    rg.setValues(vA) 
  })
}

Google Apps Script Replacing Text

1 Answers