Solved

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

Posted on 2014-07-21
5
649 Views
Last Modified: 2015-03-02
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

0
Comment
Question by:wwp_it
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:fibo
ID: 40211644
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
 

Author Comment

by:wwp_it
ID: 40211819
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
 
LVL 29

Expert Comment

by:fibo
ID: 40211946
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
 

Accepted Solution

by:
wwp_it earned 0 total points
ID: 40284186
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
 

Author Closing Comment

by:wwp_it
ID: 40294255
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
This is about my first experience with programming Arduino.
By using UNIQUE function in Google Sheets, you can get around removing duplicates like in Microsoft Excel.
In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now