setValues from array isn't recognizing the data range as 9 columns, when I have assigned 10

Question

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);


}

Related: https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept — TheMaster, Oct 31 '21 at 09:14

score 2 · Answer 1 · answered Oct 31 '21 at 00:04

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:

Thanks so much, Tanaike! This worked perfectly. I have no idea how it worked. LOL but I really appreciate it. — Brenda Harrison, Oct 31 '21 at 18:24

score 0 · Answer 2 · answered Oct 31 '21 at 05:08

function searchDialog() {
  const ss = SpreadsheetApp.getActive();
  const esh = ss.getSheetByName("Sheet1");
  const r = SpreadsheetApp.getUi().prompt('Enter Needle', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    const lns = r.getResponseText();
    const hA = esh.getRange(1,1,1,10).getValues()[0];
    const hs = esh.getRange(2, 1, esh.getLastRow() - 1, 10).getValues();
    let html = '<html><head><style>th,td{border:1px solid black;}</style></head><body><table>';
    html += `<tr><th>${hA.join('</th><th>')}</th></tr>`;
    hs.forEach((r, i) => {
      if (r[8] == lns) {
        html+= `<tr><td>${r.join('</td><td>')}</td></tr>`;
      }
    });
    html += '</table></body></html>';
    //Logger.log(html);
    SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),'Search Result');
  }
}

Demo:

Thanks so much this is perfect. What code editor are you using? — Brenda Harrison, Nov 02 '21 at 20:50

setValues from array isn't recognizing the data range as 9 columns, when I have assigned 10

2 Answers2

Modification points:

Modified script:

References: