StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORun a script on a spreadsheet, or a specific sheet within a spreadsheet, and pull data conditionally to another sheet
text
Body
copied!<p>This is my 2nd question, as the first one was solved by patt0, thanks!</p> <p>I have an on edit working script that is checking everytime I edit a sheet and, if the value of any cell of the column no. 7 is changed to "RECEBER", it will pull the entire row to another sheet. At first, I tought that would work for me, but quickly found out that the onEdit has some limitations that aren't compatible with what I want to do.</p> <p>Since I cannot rely on the onEdit, I want to run the script from a menu that was created (as suggested by patt0) whenever I want. This could be done all at once, to a whole spredsheet, and also sheet by sheet (I believe the solution to both will be similar, and both ways would be very handy for me).</p> <p>here's the code I have (not working - was adapted from the previous on edit code):</p> <p>This first part is storing the sheet name to check if it is between 1 and 31;</p> <pre class="lang-js prettyprint-override"><code>function mustBeCopied(sheetName) { var sheetNumber = parseInt(sheetName); if (sheetNumber <=31 && sheetNumber >=1) return true; return false; } </code></pre> <p>This second part probably as some issues,as it was brought from the onEdit function.</p> <pre class="lang-js prettyprint-override"><code>function RECEBER() { var ss = SpreadsheetApp.getActiveSpreadsheet(); // stores the active spreadsheet (where the script is being ran) var s = ss.getActiveSheet(); // stores the Active sheet within the stored spreadsheet var r = ss.getActiveRange(); // stores the range of rows in my document ? if(mustBeCopied(s.getName()) && r.getColumn() == 7 && r.getValue() == "RECEBER") { // if mustBeCopied, when applied to the sheet defined in the s variable is satisfied, it will check on every row if the value "RECEBER" is in its 7th column. var row = r.getRow(); // stores the row that satisfied the condition var numColumns = s.getLastColumn(); // stores the last column on the sheet ? var targetSheet = s.getSheetByName("money"); // sets the target sheet var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); // gets range on the target sheet and adds one more row for the new data to be inserted s.getRange(row, 1, 1, numColumns).copyTo(target); // writes the data into the target sheet, but I don't understand how.. } } </code></pre> <p>I'm doing a step by step analysis to see if I'm understanding what's happening..</p> <p>I'm sure this can be done quite easily, and am wiling to learn, but I can't seem to find solutions on the net.. I thought this would be already a discussed subject. It would save me a lot of time every month if I could do this automatically. It would also decrease the risk of human error, as it would be handled by computer..</p> <p>I hope someone can help me.. Thanks.</p>

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