Webhook Youtube api

teambelair · 11-21-2020 06:24 AM

Hi all,

I m trying to use the YouTube api from Appsheet to retrieve a friendlier url. I looked at this page https://developers.google.com/youtube/v3/docs/videos/list and they suggest to redirect questions on stackoverflow. See my post:
https://stackoverflow.com/questions/64940134/how-to-capture-the-list-id-automatically-generated-by-y...

I assume this can be done via a Webhook. I haven’t made any webhook yet so I am looking into some guidance on how I could try to implement the solution given on stackoverflow.

Has anyone done something similar using the YouTube api?

Thank you!

MultiTech

Hey @teambelair, I don’t know if this is possible - from a brief glance it looks like it might be, but I’m not sure how to exactly get it done.

Could explain why you’re needing to do this? Is it just that you don’t want a horrible looking URL displayed in your app?

Have you checked out HYPERLINK()?

You could also put the URL inside an action and use a button instead.

teambelair

Hyperlink could be an alternative but users would have to enter their email to get this url (I have a public app account so users do not authenticate). I would prefer that users easily access that url so they can copy and share it. To do so, I created a virtual field and display it on a form (I hide the cancel and save buttons).

The main problem is that the url is cut of after 200 characters which means that the copied url is invalid. Even if it wasn’t cut off, having a short url would be much nicer.

I also have another need of a webhook to retrieve the YouTube video duration so I am mostly interested to learn on how to integrate Appsheet with the YouTube REST api.

Thanks!

teambelair

@Steve do you recall someone integrating Appsheet with the Youtube api?

MultiTech

Why would this require the user to enter an email??

I hear you about being ineterested about the integration, but to solve your problem directly you could try the following.

Use a Show (URL) column with the HYPERLINK(“HTTPS://WWW.UGLYURLHERE.COM”, “Nice Display Text Here”) formula.

Bellave_Jayaram

Hi Alex,

I wrote a small cloud function for you.

Give this a shot -
https://us-central1-able3api.cloudfunctions.net/youtubeshorturl?url=https://www.youtube.com/watch_vi...

MultiTech

I love this community!!!

teambelair

@MultiTech_Visions this was exactly my words

Also, I quickly looked into Zapier but didn’t see a built-in workflow to do that. The trigger would be the creation of a record X on a Google sheet, calling YouTube api to get that shorter url, then updating X. I’ll do more research on that. Thanks!

MultiTech

From what I see here (about getting details from the YouTube API)

Looks like you’d need to have some intermediary to reformat things from the YouTube API result to what the AppSheet API needs.

Perhaps Zapier…

teambelair

@Bellave_Jayaram this is really cool. Thank you. How do I leverage that in Appsheet to display the result of this cloudfunction? Basically, how do I execute this url on the back end of Appsheet and share the result to my users?

Bellave_Jayaram

There’s some manual work to do this. Create an action in the detail view of type ‘External: Go to a website’ and put this as the URL to go to:

CONCATENATE(“https://us-central1-able3api.cloudfunctions.net/youtubeshorturl?url=https://www.youtube.com/watch_vi... IDs])

Where
[Related IDs] is the comma separated list.

When you click the action, it should give you the short URL. Add a real column to the sheet and copy and paste it into the relevant cell.

Automatic population to a cell is doable with an app script bound to your sheet that uses URLFetchapp.fetch()

teambelair

Thank you

When you click the action, it should give you the short URL. Add a real column to the sheet and copy and paste it into the relevant cell.

This is not going to be convenient since users are generating dynamically the id list.

Automatic population to a cell is doable with an app script bound to your sheet that uses URLFetchapp.fetch()

Yes! I am going to read this to see if I can do that: How to Integrate Google Apps Script & Trigger with an AppSheet App

Also would you mind sharing how and what tool you used to create that cloud function?

Bellave_Jayaram

I initially tried to do it using app script but it doesn’t support the HEAD method so I wrote a Python function and deployed it as a Google Cloud Function. If you need the code fragment that does the job, let me know and I will post it.

teambelair

That would be very appreciated. Thank you

I use google app script to retrieve the video duration. Works very well!
Found the script here: https://stackoverflow.com/questions/51561919/google-sheet-formula-to-convert-youtubes-api-iso-8601-d...

Bellave_Jayaram

Here is the google app script you will need. Note that you will have to pass the list of IDs in the JSON payload from the AppSheet webhook and also replace the Logger.log() line with the code to write the value to the correct cell.

function doPost  (e) {
  
  var json = e.postData.contents;
  var body = JSON.parse(json);
  var params = {"method" : "GET"};
  var response = UrlFetchApp.fetch('https://us-central1-able3api.cloudfunctions.net/youtubeshorturl?url=https://www.youtube.com/watch_videos?video_ids='+body.ids, params);    
  var code = response.getResponseCode()
  if (code === 200) {
     var location = response.getContentText('utf-8')
     Logger.log(location)
  }
}

teambelair

you will have to pass the list of IDs in the JSON payload from the AppSheet webhook and also replace the Logger.log() line with the code to write the value to the correct cell.

Sorry I don’t know what that means.

Can’t I use this function the same way i use this one to retrieve the YouTube video duration?

function getYoutubeTime(videoId){
      var url = "https://www.googleapis.com/youtube/v3/videos?part=contentDetails&id=" + videoId;
      url = url + "&key=MYKEY";
      var videoListResponse = UrlFetchApp.fetch(url);
      var json = JSON.parse(videoListResponse.getContentText());
      return json["items"][0]["contentDetails"]["duration"];
    }

I created the google app script and called it on right google sheet column:
=getYoutubeTime(P3) where P3 is the column containing the list of video ids.

Can I do something like that?
function doPost (videoIds) {

  var json = e.postData.contents;
  var body = JSON.parse(json);
  var params = {"method" : "GET"};
  var response = UrlFetchApp.fetch('https://us-central1-able3api.cloudfunctions.net/youtubeshorturl?url=https://www.youtube.com/watch_videos?video_ids='+videoIds, params);    
  var code = response.getResponseCode()
  if (code === 200) {
     var location = response.getContentText('utf-8')
     return location
  }
}

Bellave_Jayaram

Yes, you could certainly do that.

LeventK

@teambelair
You can’t reach the google function codepage as it’s owned by ABLE3 domain’s cloud account.

teambelair

I understand that. I simply went on my own google cloud platform account to try to create a cloud function.
I followed this tutorial https://rominirani.com/google-cloud-functions-tutorial-writing-our-first-google-cloud-function-a62de... but I am stuck at the second step after creating a project.

teambelair

Works great. Thank you!

function getYoutubeListId3(e){
  var params = {"method" : "GET"};
  var response = UrlFetchApp.fetch('https://us-central1-able3api.cloudfunctions.net/youtubeshorturl?url=https://www.youtube.com/watch_videos?video_ids='+e, params);  
  var code = response.getResponseCode()
  if (code === 200) {
     var location = response.getContentText('utf-8')
     return location
  }
}

Can you share with me the python script? I am going to check that tutorial: https://rominirani.com/google-cloud-functions-tutorial-writing-our-first-google-cloud-function-a62de...

teambelair

I don’t know why I can’t access the cloud function page… Need to reach out to google support.

teambelair

@Bellave_Jayaram Would you mind sharing the python code for the cloud function? I don’t want my app to rely on your api and also consumed your google api credits. Thank you!

I also observed that when I called your api twice in a row with the same input, the second call will return an error.

Bellave_Jayaram

Yes, I am not sure why it errors out but here’s the code:

import requests

def youtubeshorturl(request):

    if request.args and 'url' in request.args:
        x = requests.head(request.args.get('url'))
        if(x.status_code==303):
            return (x.headers['location'])
        else:
            return "Error retrieving data " + str(x.status_code)        
    else:
        return 'Invalid request - missing required parameter url'

teambelair

@Bellave_Jayaram: Is your cloud function still active? I am still working on it and don’t know if I’m doing something wrong or your cloud function has simply been removed? I get the error: Error retrieving data 200
I’ll let you know when I am able to create my own.

Bellave_Jayaram

Yes it’s active. It appears the HEAD request to the YouTube API is returning something else than it used to.

teambelair

Can I simply test it by entering this url on chrome?
https://us-central1-able3api.cloudfunctions.net/youtubeshorturl?url=https://www.youtube.com/watch_vi...

teambelair

I was reading back your earlier post and was wondering why this could on google app script wouldn’t work.

function getYoutubeShortURL(longURL){
  var longURLResponse = UrlFetchApp.fetch(longURL);
  return longURLResponse.getContentText()
}

I get this error message “Text result of getYoutubeShortURL is longer than the limit of 50000 characters.” Is that the Head method you were talking about?

Bellave_Jayaram

Clearly something has changed but I don’t know what and why.
The HEAD call used to return code 303 and I got the redirect URL but now the call returns code 200 (this is what I see):

{'_content': b'', '_content_consumed': True, '_next': None, 'status_code': 200, 'headers': {'X-Content-Type-Options': 'nosniff', 'Expires': 'Tue, 27 Apr 1971 19:44:06 GMT', 'Cache-Control': 'no-cache', 'Content-Type': 'text/html; charset=utf-8', 'Content-Encoding': 'gzip', 'P3P': 'CP="This is not a P3P policy! See http://support.google.com/accounts/answer/151657?hl=en for more info."', 'Date': 'Fri, 27 Nov 2020 17:41:06 GMT', 'Server': 'YouTube Frontend Proxy', 'X-XSS-Protection': '0', 'Transfer-Encoding': 'chunked', 'Alt-Svc': 'h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"'}, 'raw': <urllib3.response.HTTPResponse object at 0x7fa6b0f5bda0>, 'url': 'https://www.youtube.com/watch_videos?video_ids=1EPwH8cXB-M,QohH89Eu5iM,OMKiiSMrL5w,QohH89Eu5iM,RG3ik4VacAA,QohH89Eu5iM,e5lPjg0GhFM,QohH89Eu5iM,QsL7H856rGs,QohH89Eu5iM,lO5qDs2iEpE,QohH89Eu5iM,ZYaWAHJZXm0', 'encoding': 'utf-8', 'history': [], 'reason': 'OK', 'cookies': <RequestsCookieJar[]>, 'elapsed': datetime.timedelta(0, 0, 418204), 'request': <PreparedRequest [HEAD]>, 'connection': <requests.adapters.HTTPAdapter object at 0x7fa6b0f44358>}

teambelair

@Bellave_Jayaram: @stvar on Stack overflow was able to get the shorter url via the google app script like you suggested at first. Thanks so much for you help too. Really appreciate.

function getYoutubeShortURL(longURL)
{
    var params = {
        followRedirects: false
    };
    var response = UrlFetchApp.fetch(
        longURL, params);
    if (response.getResponseCode() != 303)
        return null;
    var headers = response.getHeaders();
    if ('location' in headers)
        return headers['location'];
    if ('Location' in headers)
        return headers['Location'];
    else
        return null;
}

Bellave_Jayaram

Cool!! No need for a cloud function!!