CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
Brenda Harrison
Brenda Harrison

Reputation: 29

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

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


}

Upvotes: 1

Views: 129

Answers (2)

Cooper
Cooper

Reputation: 64140

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:

enter image description here

Upvotes: 0

Tanaike
Tanaike

Reputation: 201723

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:

Upvotes: 2

Related Questions