Cannot get range with duration times more than 10 minutes-CodePudding

Hello I'm trying to retrieve only the durations where the time is more than 10 minutes. I'm not getting any errors but there is no result found. Seems like the comparison operator is wrong.

I'm trying to retrieve only the cells where the duration is more or equal than 10 minutes.

Here is my data.

var y = FF1.getSheetByName("sheet1").getRange('B3:B1400').getValues();

var minutes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("anothersheet").getRange('F3').getValues();

//F3 cell is equal to 00:10:00.000 and data in B3:B1400 has the same duration format

function MoreThan_10minutes(){
 //Logger.log(y);
 //Logger.log(minutes);
  if(y >= minutes) {
    Logger.log(y);
    var setvalue2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("somesheet").getRange('C3:C1400').setValues(y);  

  }else{
    var setvalue3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("somesheet").getRange('C3').setValue("no data");  
  }

CodePudding user response：

#Your code has some issues

function MoreThan_10minutes(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1')
  const y = sh.getRange('B3:B1400').getValues();
 Logger.log(JSON.stringify(y));
  const sh1 = ss.getSheetByName('anothersheet');
  const minutes = sh1.getRabge('F3').getValue();
 //Logger.log(minutes);

 //You cannot compare y and minutes like the next line because Y is a 2d array and minutes is either a single value
  if(y >= minutes) {

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the range when the duration time of the column "B" is over 10 minutes using Google Apps Script/

Modification points:

In this case, it is required to adjust the value retrieved with getValues.
- In this answer, at first, the retrieved values are converted to the RFC3339 format and the offset new Date("1899-12-30T00:00:00Z").getTime() is subtracted from the converted value. By this, the duration time can be obtained.

When these points are reflected to the sample script, it becomes as follows.

Sample script:

function myFunction() {
  const check = 10; // 10 minutes from "I'm trying to retrieve only the cells where the duration is more or equal than 10 minutes.""

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName("Sheet1");
  const timeZone = ss.getSpreadsheetTimeZone();
  const values = sheet.getRange("B1:B"   sheet.getLastRow()).getValues();
  const offset = new Date("1899-12-30T00:00:00Z").getTime(); // For Google Spreadsheet
  const res = values.reduce((ar, [b], i) => {
    const v = (new Date(Utilities.formatDate(b, timeZone, "yyyy-MM-dd'T'HH:mm:ss'Z'")).getTime() - offset) / (1000 * 60);
    if (v > check) ar.push(`B${i   1}`); // or, you want to retrieve the value as minutes, ar.push(v);
    return ar;
  }, []);
  console.log(res)
}

When this script is run using your provided Spreadsheet, the ranges of [ 'B6', 'B12', 'B13', 'B20' ] are returned as the A1Notation.
When you want to retrieve the result as the value of minutes, please modify ar.push(B${i 1}) to ar.push(v).

Note:

This sample script is for your provided sample Spreadsheet. When the Spreadsheet is changed, this script might not be able to be used. So please be careful about this. First, please test this script using your provided sample Spreadsheet.

Modification points:

Sample script:

Note:

References: