... and with Google Apps Script UI Services you can also wrap your spreadsheet in some custom UI publishing as a service or a gadget on Google Sites http://mashe.hawksey.info/2011/07/gas-staff-directory/ Martin
I've done something similar which "syncs" contacts from multiple, differently structured Google Spreadsheets, into Shared Contacts. Useful where you want to do regular exports from legacy applications like accounts/CRM/MRP.
Hi Shel. Thanks for the script. I've installed it and run the first function but keep getting the error: Attribute provided with invalid value: consumerKey (line 60)
The key is definitely right and the API's all enabled.
I'm glad people are finding the script useful. "Unknown," the error you are describing should be pretty straightforward to trouble-shoot. It means the value stored in the Script Property named "Consumer_Key" cannot be interpreted or does not match your Google domain.
To discuss the second part first, my Google domain is: http://www.google.com/a/cloudsherpas.com, so my "Consumer_Key" is just cloudsherpas.com.
To address the things that could make your key invalid, make sure your key has no leading or trailing spaces, or any other non-ASCII characters.
If everything above checks out, try updating line 60 in the script with a text value for your domain key, so it does not refer to a Script Property at all. So, like this: oAuthConfig1.setConsumerKey("cloudsherpas.com"). (Obviously use your own domain.)
Let me know if any of the above helps, and if it doesn't, I'd be curious to know which version of Google Apps you are using, for further trouble-shooting.
Thanks, JC. It's been a while, since I've looked at this blog post. The way the script is written, you have to use "Consumer_Key" and "Consumer_Secret" (with the same case and with an underscore) as your ScriptProperty names, to get the script to work.
I should also mention that you need to make sure there are no invalid characters (such as leading or trailing spaces, for example) in your key or secret as well.
Due to some other feedback I received on this post, I created a second spreadsheet that can handle larger numbers of users than this one can... (You may have noticed that the copy/pasting code in this version limited the number of users it could maintain.)
In the new one, I just remove the "production" sheet and replace it with the "staging" one, so it can handle up to about 10,000 users.
The new one also creates word "stems" of the first and last names, to support type-ahead functionality in certain gadgets I've written.
Thanks, JC. It's been a while, since I've looked at this blog post. The way the script is written, you have to use "Consumer_Key" and "Consumer_Secret" (with the same case and with an underscore) as your ScriptProperty names, to get the script to work.
I should also mention that you need to make sure there are no invalid characters (such as leading or trailing spaces, for example) in your key or secret as well.
Due to some other feedback I received on this post, I created a second spreadsheet that can handle larger numbers of users than this one can... (You may have noticed that the copy/pasting code in this version limited the number of users it could maintain.)
In the new one, I just remove the "production" sheet and replace it with the "staging" one, so it can handle up to about 10,000 users.
The new one also creates word "stems" of the first and last names, to support type-ahead functionality in certain gadgets I've written. The url is: https://docs.google.com/a/cloudsherpas.com/spreadsheet/ccc?key=0AqzFlfwsM0NrdGZreUdPV1owUjVlOFZpMktnVWQybVE
Very nice - Is it possible to restrict entries of users in this process based on organizational structure in Google Apps? Didn't see any reference for it in the API profile docs, but maybe you have some ideas...
I'm an IT graduate but I'm not working as a programmer at the moment but have been coding occassionally. I've got my current project which I was supposed to submit for the Google developers challenge but was not able to finish it. So I would like to ask some questions like how to protect my code? Just like in spreadsheet, the script can be opened by someone who owns the spreadsheet right?
I'm planning to create an app for Google drive where it creates a copy of the spreadsheet I have.
I really want to finish this project since it will help me with blogging, making money using the Google Affiliate Network and also help my fellow bloggers.
For other non-programmers (like me) who want to use this, here's what I did to get this working:
1) Enable OAuth for my domain in the google apps control panel at https://www.google.com/a/cpanel/mydomain/SetupOAuth (Replace mydomain with the appropriate value for your domain)
2) open the template (look for "this spreadsheet template" near the middle of the original post)
3) Save a copy
4) Click 'Tools', 'Script Manager'
5) Select scanUserProfiles and click 'Edit'
6) In the script manager, select 'File', 'Project Properties'
7) Select the 'Project Properties' tab
8) Add two new properties: Consumer_Key value = mydomain
Consumer_Secret value = OAuth key from Google Apps OAuth setup page
note: the property names must exactly match those shown above, and both the property names and values must not contain extra blank spaces
9) Click 'Save' in the script project properties window
10) Click 'Run' in the script project edit menu, and 'Authorize' the script as described in the original post.
I've gotten some feedback that OAuth 1.x is no longer working in this spreadsheet solution, so I've created an OAuth 2.x module that can be added to this script, using Arun Nagarajan's post on the subject on StackOverflow here.
The source code and the instructions on how to use the new module were kind of long to add as a comment to this post, so I've linked it here and at the end of this comment.
Feel free to use this OAuth2 module in any of your Apps Scripts that require OAuth. Happy coding! (https://docs.google.com/a/cloudsherpas.com/document/d/1ouTg55DitnijJfUlrSpiltERxTeowIKAvxvobrtQJxc/view)
16 comments :
thanks for info
... and with Google Apps Script UI Services you can also wrap your spreadsheet in some custom UI publishing as a service or a gadget on Google Sites http://mashe.hawksey.info/2011/07/gas-staff-directory/
Martin
I've done something similar which "syncs" contacts from multiple, differently structured Google Spreadsheets, into Shared Contacts. Useful where you want to do regular exports from legacy applications like accounts/CRM/MRP.
Shared Contacts in Google Apps via spreadsheet
Hi Shel. Thanks for the script.
I've installed it and run the first function but keep getting the error: Attribute provided with invalid value: consumerKey (line 60)
The key is definitely right and the API's all enabled.
I'm glad people are finding the script useful. "Unknown," the error you are describing should be pretty straightforward to trouble-shoot. It means the value stored in the Script Property named "Consumer_Key" cannot be interpreted or does not match your Google domain.
To discuss the second part first, my Google domain is: http://www.google.com/a/cloudsherpas.com, so my "Consumer_Key" is just cloudsherpas.com.
To address the things that could make your key invalid, make sure your key has no leading or trailing spaces, or any other non-ASCII characters.
If everything above checks out, try updating line 60 in the script with a text value for your domain key, so it does not refer to a Script Property at all. So, like this: oAuthConfig1.setConsumerKey("cloudsherpas.com"). (Obviously use your own domain.)
Let me know if any of the above helps, and if it doesn't, I'd be curious to know which version of Google Apps you are using, for further trouble-shooting.
Good luck!
I'm getting the same error it seems...
Thanks man! Been looking for this type of solution, but wasn't sure how to implement it...much better than utilizing contact info via GAS.
The error: Attribute provided with invalid value: consumerKey (line 60) can be corrected by changing the code on lines 60 and 61 to:
oAuthConfig1.setConsumerKey("Consumer_Key");
oAuthConfig1.setConsumerSecret("Consumer_Secret");
Otherwise script is great, thank you.
Thanks, JC. It's been a while, since I've looked at this blog post. The way the script is written, you have to use "Consumer_Key" and "Consumer_Secret" (with the same case and with an underscore) as your ScriptProperty names, to get the script to work.
I should also mention that you need to make sure there are no invalid characters (such as leading or trailing spaces, for example) in your key or secret as well.
Due to some other feedback I received on this post, I created a second spreadsheet that can handle larger numbers of users than this one can... (You may have noticed that the copy/pasting code in this version limited the number of users it could maintain.)
In the new one, I just remove the "production" sheet and replace it with the "staging" one, so it can handle up to about 10,000 users.
The new one also creates word "stems" of the first and last names, to support type-ahead functionality in certain gadgets I've written.
Thanks, JC. It's been a while, since I've looked at this blog post. The way the script is written, you have to use "Consumer_Key" and "Consumer_Secret" (with the same case and with an underscore) as your ScriptProperty names, to get the script to work.
I should also mention that you need to make sure there are no invalid characters (such as leading or trailing spaces, for example) in your key or secret as well.
Due to some other feedback I received on this post, I created a second spreadsheet that can handle larger numbers of users than this one can... (You may have noticed that the copy/pasting code in this version limited the number of users it could maintain.)
In the new one, I just remove the "production" sheet and replace it with the "staging" one, so it can handle up to about 10,000 users.
The new one also creates word "stems" of the first and last names, to support type-ahead functionality in certain gadgets I've written. The url is: https://docs.google.com/a/cloudsherpas.com/spreadsheet/ccc?key=0AqzFlfwsM0NrdGZreUdPV1owUjVlOFZpMktnVWQybVE
Very nice - Is it possible to restrict entries of users in this process based on organizational structure in Google Apps? Didn't see any reference for it in the API profile docs, but maybe you have some ideas...
Sorry, kinda new to this. I got this to work fine, but how do I add it to my Google Site to look like the picture you have in this article?
Thanks
Hi Shel,
I'm an IT graduate but I'm not working as a programmer at the moment but have been coding occassionally. I've got my current project which I was supposed to submit for the Google developers challenge but was not able to finish it. So I would like to ask some questions like how to protect my code? Just like in spreadsheet, the script can be opened by someone who owns the spreadsheet right?
I'm planning to create an app for Google drive where it creates a copy of the spreadsheet I have.
I really want to finish this project since it will help me with blogging, making money using the Google Affiliate Network and also help my fellow bloggers.
Thanks!
marvelous idea.
For other non-programmers (like me) who want to use this, here's what I did to get this working:
1) Enable OAuth for my domain in the google apps control panel at https://www.google.com/a/cpanel/mydomain/SetupOAuth
(Replace mydomain with the appropriate value for your domain)
2) open the template (look for "this spreadsheet template" near the middle of the original post)
3) Save a copy
4) Click 'Tools', 'Script Manager'
5) Select scanUserProfiles and click 'Edit'
6) In the script manager, select 'File', 'Project Properties'
7) Select the 'Project Properties' tab
8) Add two new properties:
Consumer_Key
value = mydomain
Consumer_Secret
value = OAuth key from Google Apps OAuth setup page
note: the property names must exactly match those shown above, and both the property names and values must not contain extra blank spaces
9) Click 'Save' in the script project properties window
10) Click 'Run' in the script project edit menu, and 'Authorize' the script as described in the original post.
I've gotten some feedback that OAuth 1.x is no longer working in this spreadsheet solution, so I've created an OAuth 2.x module that can be added to this script, using Arun Nagarajan's post on the subject on StackOverflow here.
The source code and the instructions on how to use the new module were kind of long to add as a comment to this post, so I've linked it here and at the end of this comment.
Feel free to use this OAuth2 module in any of your Apps Scripts that require OAuth. Happy coding! (https://docs.google.com/a/cloudsherpas.com/document/d/1ouTg55DitnijJfUlrSpiltERxTeowIKAvxvobrtQJxc/view)
Post a Comment