How to extract from a google sheet cells that do not contain a keyword so that it concatenate with it the corresponding data in the first row & column

Question

I have an exemple spreadsheet where i want to extract from the sheet "Survey" the cells that do not contain "OK" or "ok" and list them in the sheet "Extract" so that it takes into account the corresponding data in the frozen first row and column.

Example :

The extract would give in the first cell of the "Extract" sheet : (149,163)-G:AD

Would someone be able to help me with that please ? If at all possible.

Answer 1

Find non ok's

function wierdData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName('Sheet1');
  const r1 = sh.getRange(1, 1, 1, sh.getLastColumn()).getDisplayValues().flat();
  const c1 = sh.getRange(1, 1, sh.getLastRow(), 1).getDisplayValues().flat();
  const ro = r1.findIndex(e => e != '') + 1;
  const co = c1.findIndex(e => e != '') + 1;
  const vs = sh.getRange(co, ro, sh.getLastRow() - co + 1, sh.getLastColumn() - ro + 1).getValues();
  let o = [];
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      if(isNaN(c) && c.toLowerCase() != "ok") {
        o.push(`(${r1[ro + j - 1]},${c1[co + i - 1]})-${c}`);
      }
    })
  })
  //Logger.log(JSON.stringify(vs))
  Logger.log(JSON.stringify(o));
  let oA = o.map(e => [e]);
  osh.clearContents();
  osh.getRange(1,1,oA.length,1).setValues(oA)
}
Execution log
11:33:56 AM Notice  Execution started
11:33:56 AM Info    ["(153,168)-S:AB","(149,163)-G:AD","(150,157)-G:CB"]
11:33:57 AM Notice  Execution completed

Sheet0:

	145	146	147	148	149	150	151	152	153	154	155	156
	Exemple sheet
175	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
174	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
173	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
172	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
171	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
170	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
169	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
168	ok	ok	ok	ok	ok	ok	ok	ok	S:AB	ok	ok	ok
167	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
166	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
165	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
164	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
163	ok	OK	OK	OK	G:AD	ok	ok	ok	ok	ok	ok	ok
162	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
161	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
160	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
159	ok	OK	OK	OK	ok	ok	ok	ok	ok	ok	ok	ok
158	ok	OK	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok
157	ok	ok	ok	ok	ok	G:CB	ok	ok	ok	ok	ok	ok
156	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok	ok

Sheet1:

(153,168)-S:AB
(149,163)-G:AD
(150,157)-G:CB