How to convert duration to seconds in Google Sheets which uses IMPORTRANGE

Question

I have copied the data from Googlesheet1 to Googlesheet2 using the below query

=IMPORTRANGE("url","!A2:H")

Which has copied the data from Googlesheet1 to Googlesheet2.

In that sheet, I am having a duration column like the below image

When i used the app script to copy the data to the firestore instead of saving the duration it saves the data in DateTime format like below.

Is there any way to convert the given duration to seconds in Google sheet.

I have tried using =value(G2*24*3600) but it didn't work in the Googlesheet2 since that sheet is a clone of Googlesheet1

App script Logic:

function firestore() {
   // Firestore setup
   const email = "//client-email";
   const key = "//client-key";
   const projectId = "timesheet-aog";
   var firestore = FirestoreApp.getFirestore (email, key, projectId);

   // get document data from ther spreadsheet
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheetname = "timesheet";
   var sheet = ss.getSheetByName(sheetname); 
   // get the last row and column in order to define range
   var sheetLR = sheet.getLastRow(); // get the last row
   var sheetLC = sheet.getLastColumn(); // get the last column

   var dataSR = 2; // the first row of data
   // define the data range
   var sourceRange = sheet.getRange(2,1,sheetLR-dataSR+1,sheetLC);

   // get the data
   var sourceData = sourceRange.getValues();
   // get the number of length of the object in order to establish a loop value
   var sourceLen = sourceData.length;

   console.log('sourceLen is', sourceLen);
   // Loop through the rows
   for (var i=0;i<sourceLen;i++){

     var data = {};

     console.log('data is', sourceData);

     data.date = sourceData[i][0];
     data.name = sourceData[i][1];
     data.workFrom = sourceData[i][2];
     data.project = sourceData[i][3];
     data.phase = sourceData[i][4];
     data.task = sourceData[i][5];
     data.totalHrs = sourceData[i][6];
     data.comments = sourceData[i][7];

   firestore.createDocument("timesheet",data);

  }

}

Try putting this in cell `A1` in Googlesheet2 `={IMPORTRANGE("url","!A2:G"), ARRAYFORMULA(N(IMPORTRANGE("url","!H2:H")) * 24 * 3600)})`(edit: added `ARRAYFORMULA`) — kishkin, Mar 26 '20 at 12:31
Sorry, mistaken one column for another. Try this: `={IMPORTRANGE("url","!A2:F"), ARRAYFORMULA(N(IMPORTRANGE("url","!G2:G")) * 24 * 3600), IMPORTRANGE("url","!H2:H")})` — kishkin, Mar 26 '20 at 12:38
Yes it does, but for empty cells and for 0 durations (which is ok I assume). What do you want for empty cells? Or do you get it for every cell, even with the non-zero value? — kishkin, Mar 26 '20 at 12:50
also I had a typo: no need for the last `)` right at the end — kishkin, Mar 26 '20 at 12:53
@kishkin i tried but instead of the second's everything was 0's — Nidhin Kumar, Mar 26 '20 at 13:17
Here is the version without trailing zeros: `={IMPORTRANGE("url","!A2:F"), ARRAYFORMULA(IF(IMPORTRANGE("url","!G2:G") = "", "", N(IMPORTRANGE("url","!G2:G")) * 24 * 3600)), IMPORTRANGE("url","!H2:H")}` — kishkin, Mar 26 '20 at 13:20
You are importing a different sheet, aren't you? :) So, you should adjust for your columns. `={IMPORTRANGE("url","!A2:D"), ARRAYFORMULA(IF(IMPORTRANGE("url","!E2:E") = "", "", N(IMPORTRANGE("url","!E2:E")) * 24 * 3600)), IMPORTRANGE("url","!F2:F")}` — kishkin, Mar 26 '20 at 13:28
Alternatively you can use named ranges in your original sheet, and import them by names in the second sheet, and you will not have to adjust. — kishkin, Mar 26 '20 at 13:30
But both have the same content and even i tried the sheet which i have shared to you still i am getting the same like the image https://imgur.com/xCXI9Yc — Nidhin Kumar, Mar 26 '20 at 13:30
Not the same content: `1YwLY...` this one (the one you've shared has 2 more columns before duration. — kishkin, Mar 26 '20 at 13:32
@Nidhinkumar because you there are exact column names are used, exact column (`E:E` in the second case is Duration) is converted to seconds. If you convert another column (which is empty) you won't get anything. — kishkin, Mar 26 '20 at 13:35
@Nidhinkumar bro how did you upload the timestamp variable in firestore. When I do the same it returns an empty map object. Check this question out : https://stackoverflow.com/questions/62149933 — Nehal, Jun 03 '20 at 16:15

score 2 · Accepted Answer · answered Mar 26 '20 at 13:39

Here is the formula for A1 cell of the second sheet:

={
  IMPORTRANGE("url","!A2:F"),
  ARRAYFORMULA(
    IF(
      IMPORTRANGE("url","!G2:G") = "",
        "",
        N(IMPORTRANGE("url","!G2:G")) * 24 * 3600
    )
  ),
  IMPORTRANGE("url","!H2:H")
}

Try using named ranges for columns (A2:F, G2:G, H2:H) in the original sheet, and import them by those names so you won't need to adjust the formula where exact column names are used.

How to convert duration to seconds in Google Sheets which uses IMPORTRANGE

1 Answers1