How do I get Google Script to read the display value of a sheet's cell? It keeps returning some random date instead

Question

I am writing a script that will read the cell's value depending on what hour it is and which day it is.

This sheet looks like this: sheet looks like this:

But whenever I try to access these cells, I get returned a date instead: result of debuglog I am sure I am setting the correct column and row, because my previous log says so: column and row numbers before being sent to retrieval of cell value function

TL;DR: therefore, I suspect something is wrong with the function I call at the very latest, i.e. "getSheetValues". It should be something else, shouldn't it? But I have no idea why am I getting the dates returned.

This is my main code that I call:

function myFunction() {
  // check hour
  var d = new Date();
  var godz = d.getHours();

  // add 1 hour in case of hour being a little to early.
  // script runs at exact shift start times, so due to how getHours work, it could be 1 hour too early.
  // var godz = 17;

  console.log('Current Hour:');
  console.log(godz);

  var currentShift = 0;
  console.log('CurrentShiftStart:');
  console.log(currentShift);
  
      // set shift number based on hour
      if (godz === 9 || godz === 10) {
        currentShift = 1;
        console.log('yes1');
      } else if (godz === 11 || godz === 12) {
        currentShift = 2;
        console.log('yes2');
      } else if (godz === 13 || godz === 14) {
        currentShift = 3;
        console.log('yes3');
      } else if (godz === 15 || godz === 16) {
        currentShift = 4;
        console.log('yes4');
      }
      console.log('CurrentShiftFinal:');
      console.log(currentShift)
    
  

  // collect data using extractor
    var values = dataCollector(14, currentShift);
    Logger.log('Extracted values from the sheet:')
    Logger.log(values);

And this is the cell display value extractor code that is called in the above code:

const sheet_id = 15

function dataCollector(sheet_id, shift_number) {
  
  //access the workbook
  const wb = SpreadsheetApp.openById('1ZJGMd7_CeuA4lIPceHK5Q8sd4GNpQvAmp10Tty7BuWs'); 
  Logger.log('Loaded Spreadsheet name is:')
  Logger.log(wb.getName());

  // access all the sheets in the workboook
    var sheets = wb.getSheets();
    var sheetName = sheets[sheet_id].getSheetName();

    // Use the direct name of the sheet in the workbook.
    var dataSheet = wb.getSheetByName('Queue Manager Schedule');

  // loop through the sheets

    // for(i in sheets) {
    //   // conditional to evaluate the sheet ids
    //   if(sheets[i].getSheetId() == sheet_id) {
    //     // collect the sheet name
    //     var sheetName = sheets[i].getSheetName();
    //   }
    // }

  // access the sheets with the data
  //  var dataSheet = wb.getSheetByName(sheetName);
  
  // set the proper row depending on which shift during the day it is. 
  // rows are 4-7, so 1st shift is going to be row number 4. 
  // therefore, add number 3 to the shift_number given in the function
    var shift_number = shift_number + 3;
    console.log('Shift Number and thus row number declared:');
    console.log(shift_number);
    

  // set the proper column depending on what day it is. 
  // Monday starts on columm number 2 ("B")
  // get which day it is, and add number 1 to get proper column 
    var today = new Date();
    var dayOfWeek = today.getDay();
    var dayOfWeek = dayOfWeek + 1;
    console.log('Day of the week and thus column declared:')
    console.log(dayOfWeek);

  // extract of our data
    Logger.log('Sheetname:')
    Logger.log(sheetName)
    Logger.log('DataSheet:')
    Logger.log(dataSheet);
    //
    // convert shift number into an integer...
    var newshift = Number(shift_number);

    var valuez = wb.getSheetValues(shift_number, dayOfWeek, 1, 1);
    Logger.log('Cell value:')
    Logger.log(JSON.stringify(valuez))
    return valuez
    
}

I've expected return of the value in the specified cell. But I get returned some default-for-the-sheet date instead. I've tried looking through different answers and different functions for the past few days, but couldn't get proper result.

Answer 1

You're reading data like this:

  const wb = SpreadsheetApp.openById('1ZJGMd7_CeuA4lIPceHK5Q8sd4GNpQvAmp10Tty7BuWs');
  var valuez = wb.getSheetValues(shift_number, dayOfWeek, 1, 1);

When you point getSheetValues() at a Spreadsheet rather than a Sheet, the values will always read from the first tab in the left end of the tab bar. That tab may even be hidden.

Another issue is that the the code does not handle timezones properly. Date.getHours() gets an integer 0..23 according to the timezone of the script project rather than that of the spreadsheet. The two may differ, especially when the script is an add-on or a stand-alone script project. That means that new Date().getHours() will get an hour that differs from what you expect.

To make it work, try something like this:

function getDataByShiftAndDay() {
  const today = new Date();
  const ss = SpreadsheetApp.openById('1ZJGMd7_CeuA4lIPceHK5Q8sd4GNpQvAmp10Tty7BuWs');
  const timezone = ss.getSpreadsheetTimeZone();
  const hour = Number(Utilities.formatDate(today, timezone, 'hh'));
  const shift = Math.max(0, Math.min(3, Math.trunc((hour - 9) / 2))); // 0..3
  const dayOfWeek = today.getDay(); // 0..6
  const result = ss.getRange('Queue Manager Schedule!B4')
    .offset(shift, dayOfWeek)
    .getDisplayValue();
  return result;
}

Note that this still leaves room for some timezone inconsistency because of today.getDay(). If you run the code at a moment when the spreadsheet's timezone is on a side of midnight that differs from that of the script project's timezone, you get different day ordinals. You can tackle that with something like this:

  const dayOfWeek = Number(Utilities.formatDate(today, timezone, 'u')) - 1; // 0..6

See Utilities.formatDate() and Range.getDisplayValue().