Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

CEP google Oauth

Participant ,
Jun 03, 2019 Jun 03, 2019

I have been working CEP that can pull data from a public google sheet.

This works.

I now want, after the data has been retrieved and the script has run to change a cell value so when it goes through the loop. It will skip anything that isn't specified.

This appears to require Oauth, which i've been testing with using a http://localhost . I got this all working really well.

Now importing the code back into my panel. I realised a few things. It's not a localhost.

This isn't being sold and is bespoke for myself and the company I work for, so I don't need a webserver/website to run it off (Or do I?)

How can I get my CEP Panel get passed the Oauth so I can keep my sheets private and use the Google API (Unless anyone else has alternative suggestions)

4.9K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 04, 2019 Jun 04, 2019

You'll probably have better luck with these types of questions in a Google forum, but I did some Google Sheets Oauth stuff about a year ago. Where in your authentication are you running into an issue? Can you post a code snippet of what's breaking?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 05, 2019 Jun 05, 2019

Morning Justin,

My code works fine in a localserver

I don't have a website, so I use localhost (this works fine) in my root web server on my Mac for testing, the Oauth works fine. It retrieves the data and writes back perfectly.

So I thought I could just copy and paste this code into my CEP, soon as I hit sign in...Nothing.

I realised, my CEP doesn't sit in my web server folder on my Mac. So it's not connecting to anything as the plugin isn't localhost.

Googles security requires that, so I don't know how now that I've moved my code out of my local server into my extension panel, how to now contact and get passed Oauth.

Someone has suggested node and ngrok. Both allow you to create a localhost and port, which look likes to be a possibility, but it's a little bit over my expertise. Some appear to use an access token, but again this is slightly going over my head because I'm new to this (I'm amazed I've gotten this far, when I've taught myself all this in the last 3-6 months.)

But there's not much in examples or tutorials, just bits here and there.

Below is my code, this works great in my local server. It's just how to get it to work with my CEP that is the issue.

<html>

  <head></head>

  <body>

    <script>

i=2;

    function partOne(){

      //for (i = 2; i <= 5; i++) {

      var request = gapi.client.sheets.spreadsheets.values.get({

        spreadsheetId: 'PUT SHEET ID HERE',

        range: 'Sheet1!C'++''

}).then((response) => {

  //console.log(response.result)

  if(response.result.values == "Ready"){

    makeApiCall(i);

  }else{

    console.log();

  }

  i++;

  if(i <= 6) partOne()

});

    //}

    }

    function makeApiCall() {

      var request = gapi.client.sheets.spreadsheets.values.update({

        spreadsheetId: 'PUT SHEET ID HERE',

        range: 'Sheet1!C'++'',

        valueInputOption: 'USER_ENTERED',

        values: [ ["DONE"] ]

}).then(function(response) {

    console.log(response);

});

    }

    function initClient() {

      var API_KEY = 'PUT API_KEY HERE';  // TODO: Update placeholder with desired API key.

      var CLIENT_ID = 'PUT CLIENT_ID HERE';  // TODO: Update placeholder with desired client ID.

      // TODO: Authorize using one of the following scopes:

      //   'https://www.googleapis.com/auth/drive'

      //   'https://www.googleapis.com/auth/drive.file'

      //   'https://www.googleapis.com/auth/spreadsheets'

      var SCOPE = 'https://www.googleapis.com/auth/spreadsheets';

      gapi.client.init({

        'apiKey': API_KEY,

        'clientId': CLIENT_ID,

        'scope': SCOPE,

        'discoveryDocs': ['https://sheets.googleapis.com/$discovery/rest?version=v4'],

      }).then(function() {

        gapi.auth2.getAuthInstance().isSignedIn.listen(updateSignInStatus);

        updateSignInStatus(gapi.auth2.getAuthInstance().isSignedIn.get());

      });

    }

    function handleClientLoad() {

      gapi.load('client:auth2', initClient);

    }

    function updateSignInStatus(isSignedIn) {

      if (isSignedIn) {

        partOne();

        //makeApiCall();

        //setTimeout(function(){partTwo()},5000);

      }

    }

    function handleSignInClick(event) {

      gapi.auth2.getAuthInstance().signIn();

    }

    function handleSignOutClick(event) {

      gapi.auth2.getAuthInstance().signOut();

    }

    </script>

    <script async defer src="https://apis.google.com/js/api.js"

      onload="this.onload=function(){};handleClientLoad()"

      onreadystatechange="if (this.readyState === 'complete') this.onload()">

    </script>

    <button id="signin-button" onclick="handleSignInClick()">Sign in</button>

    <button id="signout-button" onclick="handleSignOutClick()">Sign out</button>

  </body>

</html>

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 05, 2019 Jun 05, 2019

Yea I used the token method, saves you having to log in every time. You will need Node.js enabled and you can download and require the google-api module with

var {google} = require('googleapis');

They have tuts for it up on the Google site, but here's my code for reference if you want:

/**

* the @ gdrive module allows uploading to Google Drive once authenticated

* A configuration and token file is required.

* @param {Int} number number of entries to return.

*/

var gdrive = {};

gdrive.SCOPES = ['https://www.googleapis.com/auth/drive.file'];

gdrive.TOKEN_PATH = 'token.json';

gdrive.AUTH = {};

gdrive.instance;

gdrive.build = function(token){

return new Promise((resolve, reject) => {

// Load client secrets from a local file.

fs.readFile(path.join(__dirname, 'credentials.json'), (err, content) => {

if (err) return console.log('Error loading client secret file:', err);

// Authorize a client with credentials, then call the Google Drive API.

gdrive.authorize(JSON.parse(content), token, gdrive.create);

setTimeout(function(){resolve(true);}, 1000);

});

});

};

/**

* Create an OAuth2 client with the given credentials, and then execute the

* given callback function.

* @param {Object} credentials The authorization client credentials.

* @param {function} callback The callback to call with the authorized client.

*/

gdrive.authorize = function(credentials, token, callback) {

const {client_secret, client_id, redirect_uris} = credentials.installed;

gdrive.oAuth2Client = new google.auth.OAuth2(

client_id, client_secret, redirect_uris[0]);

gdrive.oAuth2Client.setCredentials(token);

callback(gdrive.oAuth2Client);

};

/**

* Get and store new token after prompting for user authorization, and then

* execute the given callback with the authorized OAuth2 client.

* @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.

* @param {getEventsCallback} callback The callback for the authorized client.

*/

gdrive.getAccessToken = function(oAuth2Client, callback) {

const authUrl = oAuth2Client.generateAuthUrl({

access_type: 'offline',

scope: gdrive.SCOPES,

});

console.log('Authorize this app by visiting this url:', authUrl);

const rl = readline.createInterface({

input: process.stdin,

output: process.stdout,

});

// rl.question('Enter the code from that page here: ', (code) => {

prompt({

title: 'Google Token',

label: 'Enter the code from that page here:',

value: '',

}).then((value) => {

if(value === null) {

console.log('user cancelled');

} else {

console.log('result', value);

var code = value;

rl.close();

oAuth2Client.getToken(code, (err, token) => {

if (err) return console.error('Error retrieving access token', err);

oAuth2Client.setCredentials(token);

// Store the token to disk for later program executions

fs.writeFile(gdrive.TOKEN_PATH, JSON.stringify(token), (err) => {

if (err) console.error(err);

console.log('Token stored to', gdrive.TOKEN_PATH);

});

callback(oAuth2Client);

});

}

}).catch(console.error);

// });

};

/**

* Creates a Google Drive instance to be used for uploads/downloads

* @param {google.auth.OAuth2} auth An authorized OAuth2 client.

*/

gdrive.create = function(auth){

gdrive.instance = google.drive({version: 'v3', auth});

};

And then you pass in your credentials.json token with: gdrive.build(token)

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 06, 2019 Jun 06, 2019

Morning Justin,

Thank you so much for this.

This looks like the javascript file I will launch when you click on a signin feature. (Still learning as I go along).

"You will need Node.js enabled and you can download and require the google-api module with"

Is this in conjunction will the above code you have provided? (I only ask because I don't see anything in the javascript that references the node or var {google} = require('googleapis'); So assuming I need to have a look into that.) (I'll take a look for the tuts on google, I've probably been looking in the wrong place.)

I'm watching another video on node.js and google api's which hopefully tells me what I need (I'm so much better with videos)

But looking at the code you have provided, this has helped me understand that little bit more.

It's a shame I got into this late in life and have been teaching myself all this over 3 months. I have basic understanding from writing ScriptUI's and expressions with AE, but this has been a rabbit hole of a project where I have taught myself HTML, CSS, JAVASCRIPT, JQUERY (Basics) and a few other little bits. My brain is like a soggy paperbag right now and I'm so close to having this completed and working to a point of helping my workload out infinitely.

So again appreciate the help.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 06, 2019 Jun 06, 2019

Low and behold, the first time I google search "Node google api reference" first thing I get is what you've suggested.

I have googled everything like "Node Google API CEP" or "Node CEP Oauth", nothing. So I'll properly look at this next week when I don't have deadlines to complete and hopefully I'll have a fully working plugin.

Thank you again for pointing me in the right direction.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 06, 2019 Jun 06, 2019

I think this is the example I built my module from:https://developers.google.com/drive/api/v3/quickstart/nodejs

Google Drive | Node.js Quickstart

Node.js seems daunting at first, but with CEP it doesn't have to be that complicated. Just enable Node.js and mixed content in your manifest.xml,

<Resources>

    <MainPath>./ui/index.html</MainPath>

    <ScriptPath>./main.jsx</ScriptPath>

    <CEFCommandLine>

          <Parameter>--enable-nodejs</Parameter>

          <Parameter>--mixed-context</Parameter>

    </CEFCommandLine>

</Resources>

install Node.js on your machine, and you can start npm installing and requiring packages.

And yes, you'll need to require the module with a statement like var {google} = require('googleapis'); or equivalent.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 11, 2019 Jun 11, 2019

Hey Justin,

I think I'm doing something wrong. When I launch the script, I don't get a new window to sign in and get my token.

I've done tests with my terminal on localhost, this works, but not in the panel. I tried resorting to opening my invisible html server in default browser to do what I needed, but even that didn't work.

Any suggestions on what I could be doing wrong?

The node works fine, everything I alert and console.log suggests the node works fine, even testing for importing an image. However it doesn't seem to work for the googleapi.

Have you got any references to the index.js and the html that shows how it launches the browser that allows you to grab the token. Or am I missing something.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 11, 2019 Jun 11, 2019

Looking back over my code, I actually did the Google API interaction in an Electron app, and that communicated to AE / AME CEP panels via WebSockets. It should work the same way though in only CEP. I believe I created a token online, similar to how this tutorial shows it and then plugged that into the token section of the example. I sort of rewrote that section to read from a json file instead of requiring stdin/stdout.

Haven't touched this project for about a year so I'm not sure on specifics, I just know once you create a token online, plug it into your system and save it as a JSON file, you can then load that JSON file to authenticate each time so you don't have to log in anymore.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 11, 2019 Jun 11, 2019

Thank you for mentioning webSocket.
https://medium.com/adobetech/how-to-build-a-node-js-server-in-a-panel-ba1d63ea67e2

I've been working off this when you mentioned node

I've just updated my code

csInterface.openURLInDefaultBrowser("http://localhost:3200/localServer.html");


The above opens my invisible server, to log into Google Oauth. I can't play anymore on this, today, but I've managed to open my node server.

So I assume from there, I communicate that data back to my main html or index.js to use the info I want.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 11, 2019 Jun 11, 2019

Cool! If you have mixed-content enabled you don't need to build a Node.js server to communicate with the Google API, however, it might make it easier for authentication.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 12, 2019 Jun 12, 2019

You caught my interest.

I have mixed-content enabled already.

How are you able to communicate with the GoogleAPI if you don't have a local server to pull the information and communicate that back to your CEP.

Is there a javascript function that openWindow (I'm making these up), log in, feed back data. If there is, if you have a reference to that. I'd really appreciate that. That's what I've been trying to do all along hahaha, but nothing about.

You've been the only person other than the one person who suggested node in the first place to point me in the right direction. I'm amazed there isn't much more about this about.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 12, 2019 Jun 12, 2019

My method of authentication was going on the Google Dev site and creating an API token, entering that into the command line to generate a token to a JSON file and then load that JSON file each time. In other words, not super user-friendly, but it accomplished what I was doing at the time.

What you're doing may require you to build a server, I'm not 100% sure since I haven't investigated that approach yet. I'd try and follow the tutorials as best you can as that will probably give you more accurate info than I can since I haven't tried that approach yet and haven't touched this project in a while.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 12, 2019 Jun 12, 2019

I'm guessing that's how templater are doing it, They're just using a jsx (Jsxbin, otherwise this would be easy hahaha) file as a ScriptUI that opens a dev site and communicates back. No different from yours then.

With me being more of a ScriptUI and using ESTK mainly. It's transitioning over to new code, builds etc that have been not something I thought I'd ever develop.

I wanted visible .mp4, mpg demo's that animated on hover, which required CSS and JS, that's how I came about CEP. Then I wanted to import values from a document. Easy enough in JS, but then I thought if I launched this for the company I work for. there's 17 sites. locally isn't an option, which then made me look at API and Cloud. That's how I've ended up at Google sheets, because I can build a form that they can fill out. I can get it too work with Publicly published sheets, but I want them to be secure and private. SO this is how I've ended up trying to find how to get passed Oauth. Once this is achieved my CEP watches every 5 minutes, if it sees an update. It will produce new renders, then change a column that notifies whether it's been used or not. Then when it runs again 5 minutes later. It'll ignore them rows.

So ultimately the easiest solution is the best. If I can do it with a JS file that doesn't require a node. That would be awesome as I know JS better than communicating two separate html and js to talk to one another.


If you think the node is the better route I'll continue playing with this till I get it right, if not and you have any references to JS opening a dev site giving me a token that should log me in would be very appreciated.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 12, 2019 Jun 12, 2019

Cool, yea a lot of similarities to the tool I was working on, procedural rendering from spreadsheet data, pull from online, update params, render, upload renders to drive, check off on spreadsheet. I haven't used Templater before so not sure what system they're using, but there might be a simpler method without Node.js, I was already using node so it made the most sense for me to add that to my package.

When I have time to jump back on this project, will let you know if I find a solution to this.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 12, 2019 Jun 12, 2019

Cool,

I'll stick with the node as I seem to be doing better than trying without.

Templater have created a ScriptUI that does all this, but it's $2000 a year and you can only use it for one item, which I need a few. My employers can't afford that on their budget, nor can I keep producing adverts weekly for 17 sites manually.[

I built something similar in my last job, where PHP pulled data into JSON format locally and expressions read it. Only problem is that it hits the render time hugely. This is why I started developing scripts.

This still work 2 years later, but it's not efficient rendering, but it still works.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 17, 2019 Jun 17, 2019

Hey Justin,

You might have an idea how to achieve this last part.

I'm opening a browser window, this works, sign in, this works, retrieve information, this works. (image below is result)

Screenshot 2019-06-17 at 16.47.37.png

I can't for the love of me in javascript or html, send this info back to my plugin. Otherwise the whole thing works smoothly.

Any ideas? I'm beating my head against a brick wall with this.

I've tried everything other than throwing a kitchen sink at it. hahaha.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 17, 2019 Jun 17, 2019

Umm, how are you triggering the external window? Can you post that portion of your code? If there's a callback or Promise return in that function, then the result should get sent back then?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 17, 2019 Jun 17, 2019

Sorry thought I put that on my last post.

portion from index.html

<button id="sign-in">sign in</button>

portion from index.js

var signButton = document.querySelector("#sign-in");

signButton.addEventListener("click", signIn);

function signIn(){

  csInterface.openURLInDefaultBrowser("http://localhost:3200/localServer.html");

}

portion from localServer.html (This is portion that finds the bits and put it into the pre tag)

<pre id="content" style="white-space: pre-wrap;">TRY THIS</pre>

function listMajors() {

        gapi.client.sheets.spreadsheets.values.get({

          spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',

          range: 'Class Data!A2:E',

        }).then(function(response) {

          var para = appendPre(JSON.stringify(response.result.values[0]));

          //alert(response.result.values[1][0]);

        }, function(response) {

          appendPre('Error: ' + response.result.error.message);

        });

      }

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 17, 2019 Jun 17, 2019

Okay, yea openURLInDefaultBrowser() is only one directional. You'll need to setup a websocket to communicate back and forth or possibly send an HTTP request. I'm assuming your localServer.html is hosted from your app right?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 17, 2019 Jun 17, 2019

localServer is hosted from my app. Just in case, this is my folder layout based on this link
https://medium.com/adobetech/how-to-build-a-node-js-server-in-a-panel-ba1d63ea67e2

CXCS -   manifest.xml

Client -    index.html

                localServer.html

                index.js

                CSInterface.js

host - This doesn't matter

server -    main.js (This allows localServer.html to be a server)

npm modules - (Contains the node modules)
Then all the credentials in root folder.


Do you have any examples on Websocket, or HTTP requests that might point me in the right direction

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 17, 2019 Jun 17, 2019

I haven't actually hosted a server on a CEP panel this way before, but from what I can tell it acts as its own panel, with a unique panel ID (so long as you're the com.my.localserver example). If that's the case, then you can communicate between the two with Vulcan. You register events with: VulcanInterface.addMessageListener() and the send messages with VulcanInterface.dispatchMessage(). See more examples of inter-panel communication in ext.js with the After Effects Sample panel.

VulcanInterface.addMessageListener

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 17, 2019 Jun 17, 2019

Based on that would you say that there is an easier way?

I'm going ok what available source is about. A few people have said that they've hosted the server on the index.html file.

In theory I don't see an issue, but the same issue occurs that I have to open browser to sign in, but that didn't transfer to my localServer panel (I've made it visible to see what was happening)

So it appears that I have a localServer panel working indepently to localServer browser that has been opened.

So if all the above sounds fine and you can't think of a better way of setting it all up.

I'll take a look at vulcan in the morning, I've seen it in panel demos, just never knew what it was for.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 17, 2019 Jun 17, 2019

You might get better help from someone who has set up their panel this way, so you may want to post a new question focused on that topic for others to see. But from what I can tell, your localhost server is acting like another host, and you'll need some mechanism to communicate between that and your panel in your host app. Vulcan is super easy for inter-app communication. WebSockets also do this, but they take much longer to set up.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 17, 2019 Jun 17, 2019

I think I've worked out the problem.

It'll mean I need to relook at the localServer.html, but that's the easy part

The token is the bit that has been throwing me. I thought the whole script had to exist in one script.

When I use it in a terminal, this is where I've been going wrong. I type "node ." This then opens up a browser, asks me to authenticate. I hit ok and copy and paste the authenticate token in my terminal and hit return. This then gives me my token.json which allows me to access my sheets.

I Need this part to open the browser and create the token.

The rest, I can use with the token to return the information I want inside the plugin.

This is what I'm struggling with, even though I've figured out the problem. I have no idea how to do it hahaha.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines