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?
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.
@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?
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!
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.
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)
}
}
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
}
}
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
}
}
