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
@Brenda Harrison Welcome. Thank you for letting me know. I'm glad your issue was resolved. If your question was solved, please push an accept button. Other people who have the same issue with you can also base your question as a question that can be solved. And I think that your issue and solution will be useful for them. If you don't find the button, feel free to tell me. https://stackoverflow.com/help/accepted-answer — Tanaike, Oct 31 '21 at 23:09

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: