CODEWITHSUNDEEP
htmlgoogle-apps-script
ShavedApeBaby
ShavedApeBaby

Reputation: 39

passing a variable from google script to to html

I'm trying to have a video play based on a random number generated in a spreadsheet. since google sheets doesn't allow you to embed youtube videos directly I'm trying to do it through a custom dialogue box.

Here's what I have so far:

Google Script Code

    var PlayDes = sheet.getRange('g2').getValue();
    GameSheet.getRange('D4').setValue(TotYds1)
     var html = HtmlService.createHtmlOutputFromFile('Youtube')
        .setWidth(560)
        .setHeight(315);
    SpreadsheetApp.getUi() 
       .showModalDialog(html, PlayDes);

Then the HTML code is:

    <iframe width="560" height="315" src="https://www.youtube-nocookie.com/embed/7WSQGD874Yc?start=8&end=18" frameborder="0" allow="autoplay; encrypted-media" allowfullscreen></iframe>
<input type="button" value="Close" onclick="google.script.host.close()" />

I'm looking to change the video URL in the html code based on the value that's pulled from cell G2 in a spreadsheet.

Upvotes: 2

Views: 938

Answers (2)

ShavedApeBaby
ShavedApeBaby

Reputation: 39

Thanks to @JSmith for the help here's the solution I found... Google Script side

 function retrieveValue(){
  spreadsheet = SpreadsheetApp.openById('sheet ID')
  sheet = spreadsheet.getSheetByName('Data')
  var PlayDes = sheet.getRange('g2').getValue();
 return (sheet.getRange('g2').getValue());

Then on the html side

<script>
  //*********************************** retrieve the URL from spreadsheet**********************************
var myValue
  function Vidurl(value)
  {
   myValue=value
   document.write('<iframe width="560" height="315" src= '+myValue+' frameborder="0" allow="autoplay; encrypted-media" allowfullscreen><\/iframe>');
  }
  google.script.run.withSuccessHandler(Vidurl).retrieveValue(); 
</script>

One issue I had was the browser didn't like my script and was labeling it as dangerous script which I then had to over ride.

Upvotes: 1

JSmith
JSmith

Reputation: 4810

Let's say your cell is g2

create a script in your IDE like so:

function retrieveValue()
{
  ...//grab spreadsheet and sheet
  return (sheet.getRange('g2').getValue());
}

then call it on your HTML client side as so:

 <iframe id="myVideo" width="560" height="315" src="NEED VARIABLE HERE" frameborder="0" allow="autoplay; encrypted-media" allowfullscreen></iframe>
    <script>
      var myValue = null;
      function onSuccess(value)
      {
        $("#myVideo").attr('src', value);
      }
      google.script.run.withSuccessHandler(onSuccess).retrieveValue();
    </script>

NOTE

google.script.run is an asynchronous call so you'll have to wait the call is finished to get the desired value on the client side.

Upvotes: 0

Related Questions