StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POScript is working ONLY in step-by-step
text
Body
copied!<p>here the file <a href="https://docs.google.com/spreadsheet/ccc?key=0AurtxTJwggIpdG9GaE1TOV9wMEw2UHhjZVJyUXVETUE" rel="nofollow">https://docs.google.com/spreadsheet/ccc?key=0AurtxTJwggIpdG9GaE1TOV9wMEw2UHhjZVJyUXVETUE</a></p> <p>you'll find on sheet <strong>Key</strong>, what is meant to do :<br> Find in all sheets, first in column B the row where the reference (Key!B1) stands, then starting from this row, find in column C where the reference (Key!B2) stands for first<br> in other words, we are looking for the couple and make the cell in column C as activecell </p> <p>the script is working in debug mode, only<br> running it using the button in sheet Key, make it "not select" the good cell, even if the cell to be selected is found (logged in Logger) </p> <p>I use a color function<br> That mean, blue or red is the cell to be selected , and sometimes it's not selected</p> <p>the code : </p> <pre><code>function lookFor() { var ss=SpreadsheetApp.getActiveSpreadsheet(); var referSheet=ss.getSheetByName("Key"); var sheetToCheck = new Array; sheetToCheck[0]=ss.getSheetByName("work"); sheetToCheck[1]=ss.getSheetByName("user"); sheetToCheck[2]=ss.getSheetByName("tax"); sheetToCheck[3]=ss.getSheetByName("cont"); sheetToCheck[4]=ss.getSheetByName("ind"); var referenceB=referSheet.getRange("B1").getValue(); var referenceC=referSheet.getRange("B2").getValue(); //loop to work with all reference as long as there are data in the column A for (j in sheetToCheck){ sheetToCheck[j].setActiveCell("A1"); var dataToCheck=new Array; dataToCheck=sheetToCheck[j].getRange(1,2,sheetToCheck[j].getLastRow(),2).getValues(); for (i in dataToCheck){ var done=false; if (dataToCheck[i][0]==referenceB){ for (k=i;k<dataToCheck.length;k++){ if (dataToCheck[k][1]==referenceC){ //this part is user to change the color of the cell, to check if the code is working well if (sheetToCheck[j].getRange(parseInt(k)+1,3,1,1).getBackgroundColor()=="red"){ sheetToCheck[j].getRange(parseInt(k)+1,3,1,1).setBackgroundColor("blue"); } else { sheetToCheck[j].getRange(parseInt(k)+1,3,1,1).setBackgroundColor("red"); } var cell=sheetToCheck[j].getRange(parseInt(k)+1,3,1,1).getA1Notation() ; sheetToCheck[j].setActiveCell(cell); SpreadsheetApp.flush(); done=true; Logger.log("Sheet :"+sheetToCheck[j].getName()+" - Cell :"+cell); break; } } if (done==true){ break; } } } } } enter code here </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload