1

I just stumbled upon this forum seeking an answer to my own issue. I'm a newbie trying some script in google sheets for a tool I would like to try out.. I literally started it 2 days ago with the aim to track when boxes leave the store room to an office and return. What I've been able to accomplish is capture the office number, time the box leaves and when it returns but now the box column shows "undefined".. please tell me what I'm doing wrong. also, i have assigned only one office button and two box number for testing thus far.

function setValue(cellName, value) {
  SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value);
}

function getValue(cellName) {
  return SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).getValue();
}

function getNextRow() {
  return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1;
}

function setOFFICE01() {
  setValue('F25', 'Office01')
}

function set001() {
  setValue('G25', '001')
}

function set002() {
  setValue('G25', '002')
}

function addRecord(a, b, c, d) {
  var row = getNextRow();
  setValue('A' + row, a);
  setValue('B' + row, b);
  setValue('C' + row, c);
  setValue('D' + row, d);
}

function punchIn() {
  addRecord(getValue('F25'), new Date(), 'In');
}

function punchOut() {
  addRecord(getValue('F25'), new Date(), 'Out');
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Marc
  • 11
  • 4
  • The problem is not there but when value gets set. Undefined is one of js hellish pits. – Wolfeius Sep 13 '19 at 20:33
  • If you do a console.log(value); and see whats going on... – Wolfeius Sep 13 '19 at 20:33
  • 1
    Getting the hang of it now, managed to use the proper format – Marc Sep 13 '19 at 20:38
  • 2
    Ok thays better but now im missing the actual and desired output of the code. Without this i dont know whays going on... – Wolfeius Sep 13 '19 at 20:48
  • Imagine a spreadsheet containing buttons; "Office01", "001" (box1), "IN" and "OUT". A user will select the office requesting the box number and book it OUT and when done IN again, this action from the users input will create entries on the spreadsheet accordingly to track the box viewing times and to which office it was assigned to. – Marc Sep 13 '19 at 21:02
  • No idea. Its hard to relate the code with what you are telling me, in any case, undefined means it is not asigned. So either when it reads the column it doesnt return anything. – Wolfeius Sep 13 '19 at 21:15
  • Is there a way to add the value from "G25" in code above to the function 'punchIn' alongside "F25" ? – Marc Sep 13 '19 at 21:24
  • 2
    `addRecord()` takes 4 parameters, but you're only calling it with 3 arguments. So `d` will be undefined. What's supposed to be in the `D` column? – Barmar Sep 13 '19 at 21:37
  • Column D needs to show the Box number (001) – Marc Sep 13 '19 at 21:41
  • 1
    `addRecord(getValue('F25'), new Date(), 'In', getValue('G25'));` – TheMaster Sep 13 '19 at 21:55
  • If this may help, I have used a basic clock in sytem as reference and modified it slightly to what i require.. the code makes use of 3 parameters; user/time and date/in or out. – Marc Sep 13 '19 at 21:58
  • @TheMaster Many thanks.. that works perfectly ! – Marc Sep 13 '19 at 22:02
  • Do note the [process cost involved](https://stackoverflow.com/questions/35289183/long-processing-time-likely-due-to-getvalue-and-cell-inserts/57836700#57836700) and [tag:dry] principle – TheMaster Sep 13 '19 at 22:05

0 Answers0