I'm sorry if I'm making a very elementary mistake in my code here (and yes, I know it's very messy/long, I just started this stuff last month). My goal is to have an image inserted on a sheet bound to a script. When run, one of my lab techs or drivers will be able to log a case in or out. There are three possible sheets. Coach, Enrique, or Delivered. When they choose, it will ask what number to look for (ie. GDL4256, or L849). Once it finds the number in either "Enrique's" sheet or "Coach's" sheet, it will jump over the needed cells to either log the case as "in lab" or "en-route", depending on if the button state is YES or NO. (as first seen in line 36, where c can be "c+4" or "c+5") My problem is that when I run the script, it never seems to actually soft compares the inserted value to the data in the array. I wish I could find the issue here, but I couldn't find it in the debugger. Any ideas? (yes, I'm a pleb, but I think I did alright so far....)
I've omitted about 9/10 of the code, as the majority of it is repetitive, and I'm still finding ways to make it smaller, I just needa learn more about this problem first.
function Enrique(){
stat = 0;
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Case Entry', 'Is this case being logged into the lab?', ui.ButtonSet.YES_NO); //Must answer "Yes". Script will end otherwise.
if (response == ui.Button.YES) {
//User said yes, logging into the lab
var sh = SpreadsheetApp.getActiveSpreadsheet();
var ss = sh.getActiveSheet();
var cell = ss.getActiveCell();
var ui = SpreadsheetApp.getUi();
//Getting case number, not case sensitive
var response = ui.prompt('Case Entry', 'What is the case number?', ui.ButtonSet.OK);
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sheetNumber = sheets.length;
var currentSheet = ss.getIndex();//-1
//Make array
Logger.log(currentSheet);
SpreadsheetApp.setActiveSheet(sheets[currentSheet]);
cell = sheets[currentSheet].getRange(1,1);
var activeR = cell.getRow()-1;
var activeC = cell.getColumn()-1;
var data = sheets[currentSheet].getDataRange().getValues();
var step = 0;
//loop through data on sheet
var r = activeR;
var c = activeC;
var d = data[0].length;
for(;r<d;++r){
for(;c<d;++c){
step++;
Logger.log('sheet : '+currentSheet+' step:'+step+' response.getResponseText() '+response.getResponseText()+' = '+data[r][c]);
if(data[r][c]==''||(step==1&¤tSheet==currentSheet)){ continue };
if(response.getResponseText().toString().toLowerCase()==data[r][c].toString().toLowerCase()){
sheets[currentSheet].getRange(r+1,c+4).activate().setValue("Yes").setBackgroundRGB(0,255,0);
Browser.msgBox("The case has been logged into the lab on the sheet 'Enrique'");
stat = 1;
return
}
}
}
if(stat === 0)
{
Browser.msgBox("The code failed somewhere...");
}
}
}
Well, I've done lots of looking around since no one has been able to assist me (thanks).
I finally got to the sections that I needed to in my lessons to pass the information that I needed more reliably into the looper. Here is the code that I used, cause I know that someone else will have the same problem that I had. They are welcome to copy me as needed.
function Search(){
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var response = ui.alert('Case Entry', 'Is this case being logged into the lab?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
var response = ui.prompt('Case Entry', 'What is the case number?', ui.ButtonSet.OK);
var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
//The 0 argument in the function calls below is just a placeholder.
//I was originally using it for one bit to be called by "a" in the looper function
if(looper(0, response, "Enrique", 5, jumpToEnr(), "The case has been logged in under the sheet ", "#00ff00") == 1){return}
if(looper(0, response, "Coach", 5, jumpToCoach(), "The case has been logged in under the sheet ", "#00ff00") == 1){return}
if(looper(0, response, "Delivered", 2, jumpToDel(), "The case has been found in the sheet ", "#ffff00") == 1){return}
}
if (response == ui.Button.NO) {
var response = ui.prompt('Case Entry', 'What is the case number?', ui.ButtonSet.OK);
var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
if(looper(0, response, "Enrique", 6, jumpToEnr(), "The case has been logged in under the sheet ", "#00ff00") == 1){return}
if(looper(0, response, "Coach", 6, jumpToCoach(), "The case has been logged in under the sheet ", "#00ff00") == 1){return}
if(looper(0, response, "Delivered", 2, jumpToDel(), "The case has been found in the sheet ", "#ffff00") == 1){return}
}
Browser.msgBox("sorry, it seems like something went wrong...");
}
function looper(a, b, c, d, e, f, g){
e;
var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
for(n=0;n<values.length;++n){
var cell = values[n][1];
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if(b.getResponseText() == cell){sheet.getRange(n+1,d).activate().setBackground("#00ff00").setValue("Yes"); Browser.msgBox(f+c); return 1;}
}
}
function DelSearch(){
var response = ui.prompt('Case Search', 'What is the case number?', ui.ButtonSet.OK);
looper(0,response, "Delivered", 2, jumptoDel(),"I found the case in the sheet ", "#ffff00");
}
function formSubmit(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
jumpToForm2();
if(ss.getSheetByName("Form Responses 2").getRange(2,5).getValue() == "Enrique"){copyRow("Form Responses 2","Enrique");}
}
function jumpToCoach() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Coach');
Logger.log(sheet.getName());
SpreadsheetApp.setActiveSheet(sheet);
sheet.getRange('A1').activate();
}
function jumpToEnr() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Enrique');
Logger.log(sheet.getName());
SpreadsheetApp.setActiveSheet(sheet);
sheet.getRange('A1').activate();
}
function jumpToDel() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Delivered');
Logger.log(sheet.getName());
SpreadsheetApp.setActiveSheet(sheet);
sheet.getRange('A1').activate();
}