Reading Form Responses over to dashboard based on 1 cell value

Thanks to all that answered my previous query, I have to be honest I really cannot get on with Google Apps Script 🙁

What I am trying to do is relatively simple and for VBA on excel would be a doddle, but sheets is it’s own beast I guess.

What I’d like to do is…

1. read in the form responses from a google form on the tab “Comments” Cells B2:H
2. Compare the unique key responses in column B on “Comments” with a unique key in A1 on tab “DashBoard”
3. If there are match I would like to add them onto the dashboard tab in Cells G3:M20
4. This would ultimately refresh everytime the dropdown in A1 on “Dashboard” is changed

The Data on responses is always 7 columns worth, varying from strings to dates. Sometimes G & H columns will be blank

Seriously any help is appreciated here, once I’ve got this done I don’t have to touch Google Sheets anymore lol

 function bnbm() {
 var spreadsheet = SpreadsheetApp.getActive();

 var sheet = SpreadsheetApp.getActive().getSheetByName("Comments");


var range = sheet.getRange("Comments!B2:H");
var values = range.getValues();
var values2 = values.filter(String);
var range2 = sheet.getRange("Dashboard!A1")
var rowd = range2.getValues();
var rowd2 = rowd.filter(String);
var result = sheet.getRange("Dashboard!G3:M20")
   
for (var i = 0; i < values2.length; i++) {
  
if (values2[i][0] == rowd2[0][0]) {
result.setValues(values2[i][1])

  
 }
  } 
}

Answer

I don’t think that your result array can be a fixed length since you Comment goes down sh.getLastRow() so this is what I think you want:

Using ranges like B2:H is not a good idea in Apps Script because they generate a lots of null data at the end of the range which has to be filtered out.

function bnbm() {
  const ss = SpreadsheetApp.getActive();
  const csh = ss.getSheetByName("Comments");
  const dsh = ss.getSheetByName('Dashboard');
  const vs = csh.getRange(2, 2, csh.getLastRow() - 1, 7).getValues();
  const A1 = dsh.getRange("A1").getValue();
  let oA = [];//matches array
  for (let i = 0; i < vs.length; i++) {
    //if column B Comments = Dashboard A1
    if (vs[i][0] == A1) {
      oA.push(vs[i]);//save current row in matches array
    }
  }
  if ((dsh.getLastRow() - 2) > 0) {
    dsh.getRange(3, 7, dsh.getLastRow() - 2, 7).clearContent();//clear contents if any
  }
  if (oA.length > 0) {
    dsh.getRange(3, 7, oA.length, oA[0].length).setValues(oA);//move matches to dashboard
    ss.toast(`${oA.length}`, 'Matches');//number of matches
  } else {
    ss.toast('No Matches', 'Matches', 10);//no matches
  }
}

Sample Comment Data Set:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8
24	21	9	24	16	7	12	13
12	19	9	19	20	7	11	18
19	18	6	1	19	16	1	16
2	0	4	19	8	12	8	20
19	19	8	24	8	0	1	18
22	6	9	2	17	18	5	20
22	13	7	1	9	15	24	14
20	7	8	21	11	2	10	22
4	11	12	21	13	6	9	22
12	19	23	6	8	9	5	12
3	18	11	17	7	12	3	22
19	19	11	3	13	15	4	12
23	1	10	16	20	11	5	20
17	20	14	13	4	13	15	1

Results for Dashboard A1 = 19;

19
						19	9	19	20	7	11	18
						19	8	24	8	0	1	18
						19	23	6	8	9	5	12
						19	11	3	13	15	4	12
						19	24	13	11	9	19	9

With the addition of this function bnbm will run every time you make an edit in A1 of sheet Dashboard.

function onEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  if(sh.getName() == 'Dashboard' && e.range.rowStart == 1 && e.range.columnStart == 1 && e.value) {
    bnbm();
  }
  if(sh.getName() == "Dashboard" && e.range.rowStart == 1 && e.range.columnStart == 1 && e.value == null) {
    e.source.toast('Invalid Input','Error Message');
  }
}

The easiest way I’ve found to run this is to enter data into A1 (user edits only) and then click on another cell. Hitting enter will not data just forces the focus back into the empty cell and does not complete the edit.