Replicate formula with integer and referencing cell above current row

Question

I am trying to replicate the following formula in an if function in google apps script for a spreadsheet.

=IF(AND((G2/(1/96)/(INT(G2/(1/96))))=1,B2<>B1),F2, "")

Populating the cell in the D column. I am trying to get it to work so that it goes through each row and works applies this formula.

I think where I am struggling to replicate the formula is how to make it an integer and how to get the script to read the cell in the row above.

Any help would be much appreciated! See below for my rudimentary attempt at this.

function releaseTimes() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; 
var numRows = 249;  
var dataRange = sheet.getRange(startRow, 1, numRows, 16)
var data = dataRange.getValues();

for (var i = 0; i < data.length; ++i) {
var row = data[i];
var startTime = row[1];

var quarterHour = (1/96);
var committedSeats = (startTime-halfHour);
var startTime1 = sheet.getRange(startRow + i, 2);
var startTime2 = sheet.getRange(startRow + i-1, 2);
var G2 = (startTime2-quarterHour);
var G2divided = (G2/quarterHour);
var int = parseInt(G2divided);
var firstCondition = G2divided/int;

    if ( firstCondition = 1 && startTime2 != startTime1) {
    sheet.getRange(startRow + i, 4).setValue(committedSeats);
    } else { 
      sheet.getRange(startRow + i, 4).setValue(blank); 
    }
  }
}

Wheres the error? What line of code fails? We cant see your spreadsheet data so its harder to guess. Post sample data. — Zig Mandel, Dec 03 '13 at 00:50
There might be other problems, but start with this. You are making "is equal" comparisons using `=`, which is the assignment operator in javascript & Google Apps Script. You need to do those comparisons using `==` or `===`. Read [Comparison Operators](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Comparison_Operators). — Mogsdad, Dec 03 '13 at 16:09
https://docs.google.com/spreadsheet/ccc?key=0AtIvBi5LprN7dGVpbVY0N2wwYkxreFZSUkx6dnBlWkE&usp=drive_web#gid=0 - This is a link to an example of what I am trying to do, but through the script. I want something that will calculate the time and then remove any duplications. the formula mentioned in the orignal post is in the D column. It calculates the time and then removes and duplications which is what I am trying to replicate. If that makes sense? — user2788500, Dec 03 '13 at 18:20
You're not just removing (blanking, actually) duplicates, but any time that's not on a quarter-hour boundary. Is that intended? — Mogsdad, Dec 03 '13 at 19:59

score 1 · Accepted Answer · answered Dec 03 '13 at 20:54

The spreadsheet function you've provided:

=IF(AND((G2/(1/96)/(INT(G2/(1/96))))=1,B2<>B1),F2, "")

Can be expressed like this:

IF (G2 is a time ending on the quarter hour) AND (B2 is not equal to B1)
THEN
  DISPLAY F2
ELSE
  DISPLAY nothing
ENDIF

The magic number 96 appears because there are 96 quarter hours in a 24 hour period. The code would be much clearer if it just dealt with time, like this:

var firstCondition = (startTime.getMinutes() % 15 == 0);  /// BOOLEAN!

There's some confusion in your question, though. The spreadsheet formula refers to column "G", Release all Holds, for a 15-minute check. Your script, though, uses column "B", Start time, for that. Looking at your example data, there are rows like "Show 9" where the start time is a "15" while release-all is not, so you'll get different results depending on which of those two calculations is "correct". I'll assume that your script is correct, and that all times are relative to the start time.

One last point... in your script, you mix two methods of manipulating range data. You start into the (more efficient) javascript method, with var data = dataRange.getValues();, but then return to touching individual cells with statements like sheet.getRange(). That's confusing, since the first uses 0-based indexes while spreadsheets use 1-based. If possible, pick one way, and stick with it.

Script

Here's an updated version of your script. The spreadsheet data is accessed just twice - it's read and written in bulk, saving time vs cell-by-cell updates. All times are calculated relative to startTime, something you can change if you wish. Finally, javascript Date object methods are used for all time calculations, avoiding the use of magic numbers.

function releaseTimes() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headRows = 1;  // # header rows to skip
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  // Handy time definitions
  var minute = 60 * 1000; // 60 * 1000 ms
  var quarterHour = 15 * minute;
  var halfHour = 30 * minute;
  var hour = 60 * minute;

  // Process data, skipping headRows
  for (var i = headRows; i < data.length; ++i) {
    var row = data[i];
    var startTime = row[1];  // Assume all times are relative to startTime    
    var prevStartTime = data[i-1][1];

    // Test whether startTime is on a quarter-hour
    var firstCondition = (startTime.getMinutes() % 15 == 0);

    // Committed blank unless unique quarter hour start time
    // NOTE: You can't compare javascript time objects using ==
    // See: http://stackoverflow.com/a/7961381
    if ( firstCondition && (prevStartTime - startTime) != 0) {
      row[3] = new Date(startTime.getTime() - halfHour);  // Monitor Committed Seats. Return to sale orders that
    } else {                                              //have been in the basket for over 1 hour.
      row[3] = ''; 
    }
    row[4] = new Date(startTime.getTime() - hour);        // Release Press Holds bar 2
    row[5] = new Date(startTime.getTime() - halfHour);    // Release all Company Holds. Release Venue Holds bar 2
    row[6] = new Date(startTime.getTime() - quarterHour); // Release all remaining holds
  }
  // Write out updated information
  dataRange.setValues(data);
}

Result

As you can see, the result is a bit different from your initial example, because of the decision to base decisions on startTime.

Screenshot

Replicate formula with integer and referencing cell above current row

1 Answers1

Script

Result