Need help with Google Script for selecting Column A on next row when scanning

I am in need of some desperate help if anyone would be so kind. I am new here, so I apologize about the code formatting below; I am not sure if I did it correctly.

I am going to be scanning in nearly 2000 student IDs (column A) and Chromebook serial #s (column B) into a Google Spreadsheet next week. I have a handheld new scanner and it works as expected. It automatically presses enter and tab after each scan. After the scan in column B (serial #), I would like the active cell to change to the next row and select column A to ready itself for the next student ID.

I have searched high and low through Google's documentation and others' experiments. I found code to set cells on another sheet and adapted it to what I need but I can't get it to work properly. I also found some tips on mrexcel, but excel is not an option.

If you look at the code below, when I create a button on the sheet and aim it at the function goToSheet(), then click the button, it correctly calls the function goToCell and selects column A on the next row from whatever the currently selected cell in column B is.

When I manually enter a value into column B or scan one in, the onEdit change event activates, calls the goToCell correctly and all operates as it should but the selected cell does not become activated.

I have tried everything I can think of including removing all columns except A & B. Can someone please help? Thank you in advance for your time.

function onEdit() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var rows = sheets.getActiveCell().getRow();
  var cols = sheets.getActiveCell().getColumn();
  var cellvalue = sheets.getRange("B"+rows).getValue();
  //SpreadsheetApp.getUi().alert('current row: '+rows);

  if (cols == 2 && cellvalue != null)
    {
      var rowsplus = rows + 1;
      SpreadsheetApp.getUi().alert('going to jump '+rowsplus+' 1');
      goToCell("Sheet1",rowsplus,1);
    }
}

function goToSheet() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var rows = sheets.getActiveCell().getRow();
  var cols = sheets.getActiveCell().getColumn();
  var rowsplus = rows + 1;
  SpreadsheetApp.getUi().alert('going to jump '+rowsplus+' 1');
  goToCell("Sheet1",rowsplus,1);
}

function goToCell(sheetName, row, col) {
  SpreadsheetApp.getUi().alert("jumping "+row+" "+col);
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  SpreadsheetApp.setActiveSheet(sheet);
  range = sheet.getRange(row,col);
  SpreadsheetApp.setActiveRange(range);
  SpreadsheetApp.getUi().alert('jumped');
;}

Open in new window

wwp_itAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bernard S.CTOCommented:
1 - You should check what happens with sheet in goToCell.
It seems that on occasions  getSheetByName fails https://code.google.com/p/google-apps-script-issues/issues/detail?id=2559.
This does not seem the case related in https://code.google.com/p/google-apps-script-issues/issues/detail?id=1758 since you clearly have no trailing or leading spaces.

2 - Since all seems fine, I would test for some oddity such as values passed to functions, and would defensively test if passing values as variables rather than constant does change anything, eg,
      mycols=1;
      mysheet='Sheet1';
      goToCell(mysheet,rowsplus,mycols);


3 - Just in case the cursor moves after entering a value, I would sligthly revise the conditional to protect me additionnally. That would give a code like
  if ( (2 == cols && cellvalue != null) OR (2 < cols) )
    {
      var rowsplus = rows++;
      SpreadsheetApp.getUi().alert('going to jump '+rowsplus+' 1');
      mycols=1;
      mysheet='Sheet1';
      goToCell(mysheet,rowsplus,mycols);
    }

Open in new window

0
wwp_itAuthor Commented:
Hello Fibo,

Thank you for your help.

This is definitely a bug in the new Google sheets as it works in the old sheets (although way too slow to actually be effective). Last night I read some of the same bugs that you mentioned.

Thank you for your code. For some reason:
var rowsplus = rows++;

Open in new window

does not return the correct row so I left it as rows + 1.

I took your suggestion and passed variables along to GoToCell and verified the variables were passed correctly by dumping to browser alert box.

if ( (2 == cols && cellvalue != null) OR (2 < cols) )

Open in new window

returned and error for me stating a parenthesis was missing and I am not sure why as it looks correct to me.

From what I read, I really think that the issue has to do with the setActiveSheet method and the way it interacts with the onEdit event change. I don't mind doing it by sheetID but I couldn't seem to get that to work either.

Is there a work around for the bug? Thank you so much for your help!

-Allan
0
Bernard S.CTOCommented:
For some reason:
var rowsplus = rows++;
does not return the correct row so I left it as rows + 1
Stupid me! your initial code was ok mine changes the value of rows and is grammatically incorrect!

if ( (2 == cols && cellvalue != null) OR (2 < cols) )

Open in new window

Hmmm... maybe it needs all parenthesis, that would be
 if ( ((2 == cols) && (cellvalue != null)) OR (2 < cols) )

Open in new window

0
wwp_itAuthor Commented:
After trying for hours to get this to work, I tried in the old version of Google Sheets and my original code functions properly. So, it is definitely the bugs above in the new Google Sheets preventing this from working.

As a workaround, I did this:

1. Highlight Range of Cells in columns A & B you will be inputting Data. Make sure you exclude Row 1. You will see a blue outline on the selected range. You should grab about 30-35 rows at a time so that you can scan an entire class set without stopping. Add more rows if needed.
 
2. Scan - The columns will toggle back and forth and descend through your selection.
 A. Student ID Barcode
 B. Device Serial Number
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wwp_itAuthor Commented:
This is not really a solution but a work around. The original code is correct but due to Google Sheets bugs, it just doesn't work at the current time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.