StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POpull data to a specific sheet within a document
text
Body
copied!<p>I'm a new to Script editor on google spreadsheet and not much of a programmer. I've been working with google docs for sometime now and it has become an important tool in my daily activity. What I'm trying to do, is the following: Seek a whole document (with severall sheet such as "1", "2", "3", and so on, corresponding to the number of days a month can hold) and if the column 7 shows a determined value (in my case, it will be RECEBER), pull all the data in that row and write onto a sheet created for this purpose. What's happening is that I'm using the the event onEdit to trigger this function. At first sight, it would be ideal, but in my case, I copy a lot of data from other spreadsheets and the paste command does not trigger the onEdit event. Instead, I have to edit the cell manually in order to get that row copied onto the other sheet. I could run it just once, once tha whole days of the month were filled and there were any changes left to do, but what I really want to do is to make it immediately, as soon as the content is inserted into the spreadsheet.</p> <p>There's also another problem with my code, it has to be fit and adapted to all the other sheets, as the if clause only performs the full operation if the active sheet equals "1". Anyway, I believe there is a simple solution to this.</p> <p>Here's the code found on the net that already took me halfway:</p> <pre><code>function onEdit(event) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var s = event.source.getActiveSheet(); var r = event.source.getActiveRange(); if(s.getName() == "1" && r.getColumn() == 7 && r.getValue() == "RECEBER") { var row = r.getRow(); var numColumns = s.getLastColumn(); var targetSheet = ss.getSheetByName("money"); var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 1, 1, numColumns).copyTo(target); } } </code></pre> <p>I'll appreciate all the help you can give. Thanks in advance.</p> <p>Diogo Sousa</p> <p>-- Updated 12Oct --</p> <p>I've changed the logics on my code, and as patt0 suggested, I run the script from the menu created. I've tried adapting the code, but I believe there's some section wrong. The script runs, but isn't writing anything at all on my target sheet.</p> <p>here's the code:</p> <pre><code>function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ {name: "RECEBER", functionName: "RECEBER"} ]; ss.addMenu("Scripts", menuEntries); } function mustBeCopied(sheetName) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheetNumber = parseInt(sheetName); if (sheetNumber <=31 && sheetNumber >=1) return true; return false; } function RECEBER() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var s = ss.getActiveSheet(); var r = ss.getActiveRange(); if(mustBeCopied(s.getName()) && r.getColumn() == 7 && r.getValue() == "RECEBER") { var row = r.getRow(); var numColumns = s.getLastColumn(); var targetSheet = ss.getSheetByName("money"); var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 1, 1, numColumns).copyTo(target); } } </code></pre> <p>the function Mustbecopied, from what I believe, only sets the range of sheets (1 to 31) eligible; the function RECEBER will determine if the value on the column 7 will satisfy the condition (RECEBER), so it can retrieve all the row information and move it to the target sheet.</p> <p>Maybe the trouble is the active sheet thing.. can I us eit in my own advantage and apply the script to the selected sheet? Also, if I could have both option (whether to apply it to the selected sheet or to the whole document), that wouuld be great and simplify my daily work a lot!</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