Google Sheets move cursor onEdit trigger based on cell content

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP


Google Sheets move cursor onEdit trigger based on cell content



I am trying to write a Google Sheets Apps Script function that checks the content of the current active cell, matches it to the content of another cell, then moves the cursor according to the result of that check.



For a spreadsheet as this example one:
https://docs.google.com/spreadsheets/d/1kpuVT1ZkK0iOSy_nGNPxvXPTFJrX-0JgNmEev6U--5c/edit#gid=0



I would like the user to go to D2, enter a value followed by Tab, then while the active cell is in E2, the function will check if the value in D2 is the same in B2. If it is, stays in E2.
Then we enter the value in E2 followed by Tab, the function checks if it's the same as C2, if it is, then moves from F2 down and left twice to D3. So if all the values are entered correctly, the cursor zig-zags between the cells in D, E and F as shown below:



enter image description here



The closest I could find is the answer to the one below, but it involves clicking on a method in the menu each time:



Move sheet rows on based on their value in a given column



I imagine the function could be triggered at the beginning of editing the document, then it keeps moving the cursor until the document is completed, at which point the function can be stopped.



Any ideas?



EDIT: what I've tried so far:



I have managed to change the position to a hard-coded position 'D3' and to create a function that moves one down with these functions:


function onOpen() {
var m = SpreadsheetApp.getUi().createMenu('Move');
m.addItem('Move to D3', 'move').addToUi();
m.addItem('Move to one below', 'move2').addToUi();
m.addItem('Move down left', 'move_down_left').addToUi();
}

function move() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var range = s.getRange('D3');
s.setActiveRange(range);
}

function move2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var r = s.getActiveRange();
var c = r.getCell(1,1);
var target = s.getRange(c.getRow() + 1, c.getColumn());
s.setActiveRange(target);
}

function move_down_left() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var r = s.getActiveRange();
var c0 = r.getCell(1,1);
var r1 = s.getRange(c0.getRow(), c0.getColumn() - 1);
var c1 = r1.getCell(1,1);
var r2 = s.getRange(c1.getRow(), c1.getColumn() - 2);
var c2 = r2.getCell(1,1);
if (c1.getValue() == c2.getValue()) {
var target = s.getRange(c1.getRow() + 1, c1.getColumn() - 1);
s.setActiveRange(target);
}
}



This question has not received enough attention.





Sure, you can do that with onEdit. You could use a menu-triggered function to "initiate" the flow. Consult the Spreadsheet Service documentation (particularly the Range and Sheet classes) for the relevant methods, such as value accessors, etc. you probably want to handle both the "entered successfully" and "not entered successfully" cases - often the natural behavior is to move down or right, depending on the user keypress.
– tehhowch
Jul 20 at 16:00


onEdit


Range


Sheet





Are you basically asking for someone to do this for you? What do you envision the answer being? A turnkey solution? You don't show any effort in solving this yourself.
– tehhowch
22 hours ago





Would it help if I try to write the functions and copy+paste what I have even if it's not working?
– 719016
21 hours ago





@71 Yes,It would.
– I'-'I
19 hours ago









By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Popular posts from this blog

Arduino Mega cannot recieve any sketches, stk500_recv() programmer is not responding

Visual Studio Code: How to configure includePath for better IntelliSense results

C++ virtual function: Base class function is called instead of derived