I'm trying to use setValues from an array I have 10 columns of data so [9] in the array. I keep getting an app script error about having 10 columns of the range but only 1 column of data. I'm seeing 3 [] brackets around my array. One set of brackets around the whole array and then two [] around each row of data. Not sure if this is the problem.

// Search Records for Contact ID Number
function searchExistingCustomers() {
const ss = SpreadsheetApp.getActiveSpreadsheet();  //get Active Sheet
const searchSheet = ss.getSheetByName("Search Sheet");   //get Sheet "Search Sheet"
const existingSheet = ss.getSheetByName("Existing Customers"); //get Sheet "Existing Customers"
var lastNameSearch = searchSheet.getRange("B2").getValue(); //last name search string
var columnIndex = 9
var lRow = existingSheet.getLastRow();
var lColumn = existingSheet.getLastColumn();
var columnArray = existingSheet.getRange(2,columnIndex,lRow).getValues(); //Get Values in Array 1st row is header row
var valuesFound=false; //variable to boolean value

// examin the values in the array
var rowSearchValue = 0;
let searchResults = []; 
for (var y = 0; y < lRow; y  ) {
   if(columnArray[y] == lastNameSearch){
     var searchRow = (y   2);
     var searchInfo = existingSheet.getRange(searchRow,1, 1, 10).getValues();
     searchResults.push(searchInfo);
   rowSearchValue = (rowSearchValue  1);
   }
}

//Send Search Results to Search Spreadsheet

searchSheet.getRange(5,1,rowSearchValue,10).setValues(searchResults);

Logger.log(ss);
Logger.log(existingSheet);
Logger.log(existingSheet);
Logger.log(lastNameSearch);
Logger.log(lRow);
Logger.log(lColumn);
Logger.log(columnArray);
Logger.log(rowSearchValue);
Logger.log(searchRow);
Logger.log(searchResults);


}

CodePudding user response：

Modification points:

• getValues() of Class Range returns 2 dimensional array. In your script, var searchInfo = existingSheet.getRange(searchRow, 1, 1, 10).getValues(); is pushed to an array with searchResults.push(searchInfo);. In this case, searchResults is 3 dimensional array. I thought that this might be the reason for your issue. When your script is modified by reflecting this, please modify it as follows. By this modification, I think that your error can be removed.

  • From

      searchResults.push(searchInfo);
    

  • To

      searchResults.push(searchInfo[0]);
    

• But, when getValues is used in a loop, the process cost becomes high.

When these points are reflected in your script, it becomes as follows.

Modified script:

function searchExistingCustomers() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();  //get Active Sheet
  const searchSheet = ss.getSheetByName("Search Sheet");   //get Sheet "Search Sheet"
  const existingSheet = ss.getSheetByName("Existing Customers"); //get Sheet "Existing Customers"
  var lastNameSearch = searchSheet.getRange("B2").getValue(); //last name search string
  var columnIndex = 9
  var values = existingSheet.getDataRange().getValues();
  var res = values.reduce((ar, r) => {
    if (r[columnIndex - 1] == lastNameSearch) ar.push(r.splice(0, 10));
    return ar;
  }, []);
  searchSheet.getRange(5, 1, res.length, res[0].length).setValues(res);
}

References: