Solved

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

Posted on 2014-07-21
5
678 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Google is more than just a search engine. Over the years the company has developed a wide range of online services that are readily available to all users. This article highlights how one can use Google services for simple project management.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This Micro Tutorial will demonstrate importing calendar invites from events such as webinars into your Google Calendar.

840 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