3

I have a google sheet script that fetches youtube's video durations. The problem is the time data is in the ISO 8601 format.

For example:

PT3M23S

The formula I'm using right now does a good job converting this into a more readable format. 

=iferror(REGEXREPLACE(getYoutubeTime(B20),"(PT)(\d+)M(\d+)S","$2:$3"))

It converts the above into a more readable format 3:23

Now the issue at hand is if the duration of the video is exactly 3 minutes or if the video is shorter than 1 minute regexreplace doesn't reformat it.

Instead it reads

PT4M OR PT53S

Is there a way to edit the formula to address each variant that potential could occur?

Where it would format PT4M into 4:00 or PT53S into 0:53

Lastly, if the seconds in the duration are between 1-9 the API returns a single digit value for the seconds. Which means the formula above will look wrong. For example, PT1M1S is formatted into 1:1 when it should read 1:01

It would be great if the formula could account for the first 9 seconds and add a 0 to make it more readable.

Thanks for reading this far, if anyone could help me out I'd very much appreciate it.

Just in case its easier to do this within the script itself here's the custom script that retrieves the video duration.

function getYoutubeTime(videoId){
  var url = "https://www.googleapis.com/youtube/v3/videos?part=contentDetails&id=" + videoId;
  url = url + "&key=";
  var videoListResponse = UrlFetchApp.fetch(url);
  var json = JSON.parse(videoListResponse.getContentText());
  return json["items"][0]["contentDetails"]["duration"];
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
darkshaed
  • 69
  • 8
  • 3
    @pnuts the format shown is an ISO 8601 formatted duration, rather than a date/time - https://en.wikipedia.org/wiki/ISO_8601#Durations – Cameron Roberts Jul 27 '18 at 16:39

3 Answers3

2

Is very ugly, but seems to work for the examples provided:

=iferror(left(mid(A1,3,len(A1)-2),find("M",mid(A1,3,len(A1)-2))-1)*60,0)+substitute(REGEXreplace(mid(A1,3,len(A1)-2),"(.+M)",""),"S","")

Outputs seconds, eg 203 from PT3M23S. To change to 00:03:23 wap the above formula in ( ... )/86400 and format result as Time.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Great, I was able to use this and convert the seconds to the format I needed! I used this formula to finish grab the value your formula converted the number to and changed the format to duration which gave me exactly what I needed. `=iferror(ArrayFormula(if(len(E4:E),time(,int(E4:E/60),mod(E4:E,60)),)))` – darkshaed Jul 27 '18 at 20:13
2

Script Solution: 

function iso8601HMparse(str) {
  return str.replace(/PT(\d+(?=M))?M?(\d+(?=S))?S?/g,function(mm,p1,p2){//regex to get M and S value
    return [0,p1,p2].map(function(e){
      e = e ? e:0;
      return ("00"+e).substr(-2); //fix them to 2 chars
    }).join(':');
  })
}

Splice it in your script like: 

return iso8601HMparse(json["items"][0]["contentDetails"]["duration"].toString());

Spreadsheet Function: 

=TEXT(1*REGEXREPLACE(REGEXREPLACE(A1,"PT(\d+M)?(\d+?S)?","00:00$1:00$2"),"[MS]",),"MM:SS")
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • it doesn't work for video over 1 hour. Eg.: video on YouTube has 1:13:02 and function returns `00:00:001H13M2S` – pancake Jan 16 '21 at 23:31
  • 1
    @pancake Try this [answer](https://stackoverflow.com/a/29153059) instead. If that doesn't work, I'll fix the regex for hours as well. – TheMaster Jan 17 '21 at 06:13
1

Late to the party, but here's what I'm using:

=TIMEVALUE(
IFERROR(TEXT(MID(A1,3,FIND("H",A1)-3),"00"),"00")&":"&
IFERROR(TEXT(MID(A1,IFERROR(FIND("H",A1)+1,3),FIND("M",A1)-IFERROR(FIND("H",A1)+1,3)),"00"),"00")&":"&
IFERROR(TEXT(MID(A1,IFERROR(FIND("M",A1)+1,3),FIND("S",A1)-IFERROR(FIND("M",A1)+1,3)),"00"),"00"))

You can also stick ARRAYFORMULA in front of this and change A1 to a a column to get values for a whole list of them.

Jay Adama
  • 21
  • 2