Stream

Join this community to post or comment

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you are creating add-ons that generate SVG and you need to embed the result in a Google Sheet or Doc, you'll need to convert it to another format first. Trouble is that most svg conversion libraries don't support all svg capabilities. Here's how to do it using your browser API.

#gde   #googleappsscript   #blogpost   #javascript   #svg  
3
Add a comment...

Zig Mandel

Tips & tricks  - 
 
This small sample shows two different ways to pass initialization data to an html client, one uses HtmlOutput and the other uses HtmlTemplate

https://github.com/zmandel/htmlService-get-set-data
7
Andrew Roberts's profile photoZig Mandel's profile photo
2 comments
 
+Andrew Roberts thanks Andrew! updated.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Being able to use worksheet functions from apps script is something that might be useful for some. D3.js has some of those functions already implemented in JavaScript, and can be used server side in addition to its more normal use for client side charting and data manipulation. Here's a library you can use to get at some of D3's great features from server side apps scripts.

#gde   #googleappsscript   #blogpost  
4
1
Add a comment...

Ben Fletcher

Tips & tricks  - 
 
Recursive solution to calling a custom function with either a single value or multiple values (e.g., placing a range in parameter where called in the sheet). Reading +Bruce Mcpherson's tips, running on a range is much faster than copying a custom function down a row.

function myFunction (input) {
if (Array.isArray(input)) {
return input.map(myFunction);
} else {
return yourFunction(input);
}
}

Returns a single value when passed a single value or an array of the same size of the called range values.

I haven't seen a way to wrap a custom function so that it works with ranges or values. Maybe it's in the documentation or here or stackoverflow, but my searches turned up nothing. (Or maybe it's so basic everyone but me already knew how to do this!)

I'm a GAS and JS beginner, so maybe the code is flawed or could be improved, but it works for me. If anyone has improvements, I'd love to get them or learn better techniques.
1
Ben Fletcher's profile photoBruce Mcpherson's profile photo
5 comments
 
Yes - i'd expand it a bit to allow multiple arguments to be passed, and show an example of how it might be used.
for example .. this enhanced version would allow you multiply a cell or range of cells by a single value.

/**
* demo recursion in custom function
* examples of use
* =multiplyBy (10 , a1) a single result a1 * 10
* =multiplyBy (10 , a1:a3) a column, each multiplied by 10
* =multiplyBy (10 , a1:c3) a row, each multiplied by 10
* =multiplyBy (10 , a1:c3) a matrix, each multiplied by 10
*@param{number|Array.number} values
*@return{number|Array.number} the numbers multiplied by a factor
*@customfunction
*/
function multiplyBy (factor, values) {

// we need another function to allow additional arguments
// to the custom function to be available through closure
return recurse (values);

// this will either carry out the caclulation
// or call itself for each element of the array
function recurse (values) {
if (Array.isArray(values)) {
return values.map(recurse);
}
else {
return values * factor;
}
}
}
Add a comment...

Alan Wells

Tips & tricks  - 
 
Stress test your Apps Script code running from a Form submission.  Here is a shared file to an Apps Script Web App that blasts multiple form submissions in rapid succession.  It takes the last form submission in the Google Form, and creates a new Form submission.  So, if you want to know how to do that, this is a good example.  Make a copy of the file so that you you have a copy in your Drive and you are the owner.

https://script.google.com/d/16zPGLMa-ktHrdxMKRB5a6qhlsJXxdRisUEwGjCfFrY3VQnvm8DVRWgWO/edit?usp=sharing
3
2
Alan Wells's profile photoJean-Pierre Verhulst's profile photo
2 comments
 
Thanks for sharing +Alan Wells. Might come in handy one day. :-)
Add a comment...

Alan Wells

Tips & tricks  - 
 
Here is a shared Apps Script file published as a Web App with an implementation of a drag-able vertical dividing line.  For example, if you wanted a sidebar that could be resized by dragging the divider. 

Here is the live example:

https://script.google.com/macros/s/AKfycbxglmWb4eKD3ipqHywKfFrAm6qYglAr6dsOjT_AIs_nh_ntLXXF/exec

Here is the publicly shared Apps Script file:
https://script.google.com/d/1R4GczI_Lr7mRGAILX78Npfdn9JeOcZ7y5EB4bj0JoS39q-Gei9AYtl2v/edit?usp=sharing

The original code is from a JS fiddle:

http://jsfiddle.net/XNJBH/embedded/result,html,css,js/
5
Alan Wells's profile photo
 
I've added a header section with a file menu, toolbar, and sidebar and main section headers section.  It's very basic, but probably could be used as a starting point or learning example.
Add a comment...

Martin Hawksey
moderator

Tips & tricks  - 
 
Nice post from +Nico Miceli on getting Google Analytics data into Google Sheets
 
New Post!

If you have auto updating google analytics daily reports that are hitting sampling, pull it in batches! Check out my new post on how to do that with apps script.

http://nicomiceli.com/google-analytics-data-with-google-apps-script/

#gde #analytics #blogpost #googleanalytics

1
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I published something lately that allowed you watch for and react to server side data and positional changes in Sheets from client side add-ons. Well here's an update so it now does the same thing for Docs. It'll call you back if the document content, the selection or cursor changes and you can watch parts or all of the document for data changes.

#gde   #googleappsscript   #blogpost  
3
2
Add a comment...

Thomas Mills Hinkle

Tips & tricks  - 
 
Today I discovered this gem: google has two different IDs for each form response, and you will get different ones when you use form.getResponses() and when you use form.getResponse() -- isn't that handy?

responses = form.getResponses()
responses.forEach( function (resp) {
idNumber1 = resp.getId();
idNumber2 = form.getResponse(idNumber1).getId();
Logger.log('%s != %s',idNumber1, idNumber2);
}); // end forEach

Thanks google...
4
1
Alan Wells's profile photoThomas Mills Hinkle's profile photo
2 comments
 
Yep... well, in the work around was knowing how to get both IDs and check for them... rather than checking against a simple ID against a list of known FormIDs, I end up checking BOTH IDs against a known list. So far, so good, but I'm not at all confident this won't bite again soon :)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I recently posted a way of detecting server side data changes from the client

A little update here with an idea from +Romain Vialard

To avoid unnecessary polling, it now detects whether the user is actually looking at the document. If not - it suspends all polling activity till he is. Simple .. but effective.. so if you are using client/serverwatcher .. there is a new version out that does that.

http://ramblings.mcpher.com/Home/excelquirks/gassnips/gaswatch

#gde #googleappsscript #blogpost
5
Add a comment...

Riël Notermans
moderator

Tips & tricks  - 
 
Sometimes I want to get the user email address when running the script as a particular user, and anonymous users are opening it. This can be done by authenticating the user client side, and do a google.script.run or an execution API call, with passing the access_token.

This function can be used to have Google Apps Script fetch the user details with the token as the input:

function getInfo(token) {
var info = CacheService.getScriptCache().get(token)
if (!info) {
var info = UrlFetchApp.fetch('https://www.googleapis.com/oauth2/v3/userinfo?access_token='+token).getContentText();
CacheService.getScriptCache().put(token, info)
}
info = JSON.parse(info);
return info;
}

Now, is there an advanced service, or is there interest in having one, because now I am running in URLFetch limits when many users do this.

Please don't vote if you never ran into the need of getting an emailaddress from an anonymous user, but if you did, please let me know if this solution could help you. After that I suggest we put in a feature request to have userinfo / token exchange as an advanced service.
6 votes  -  votes visible to Public
Yes, this would help me a lot
83%
No, I found another workaround
17%
3
B. S. Hogeman's profile photoRiël Notermans's profile photo
3 comments
 
B.S. not sure what exactly you mean but in any case you would need the user consent.

Users logged in into the school domain is easier, the script can use Session.getActiveUser()
Add a comment...

Alan Wells

Tips & tricks  - 
 
New Version of Forms Blaster.

https://script.google.com/d/16zPGLMa-ktHrdxMKRB5a6qhlsJXxdRisUEwGjCfFrY3VQnvm8DVRWgWO/edit?usp=sharing

Designed for testing code that is triggered by Form submissions.

New Features:

* List of Form files in drop down list
* Input box for number of submissions to make
* Updated Help Section
* Ability to show last form response answers
* Builds an HTML input Form from the Google Form
* Ability to input new values for the Form submission
* Feature to show an increment counter on the end of answers

Free to copy and use.  Good example of a Web App built with Apps Script.

This Web App will submit responses to a Google Form without the need to open the Google Form.  Features easy to use interface and built in Help information.
1
1
Add a comment...

Alan Wells

Tips & tricks  - 
 
Seems that there is no documentation for Apps Script "Object Service."  What does:

    Object.freeze(obj)

Do?

OR:

    Object.seal(obj)
1
Bruce Mcpherson's profile photoJonathon Broughton's profile photo
4 comments
 
They have been around since 5.1 / 1.84 as well so there is a fair amount of online coverage.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
One of the tricky problems to solve when writing a book, or creating a video series is to think of example data to use that is of some interest. I'm working on another video course right now, and was pondering on with that very thing, when a question on this forum about the Itunes API reminded me that there are acres of data there I could play with, and its a public API. So here's a library wrapper for the Itunes API. I thought you might find it useful.

#gde   #googleappsscript   #blogpost  
4
3
Raul Flores, Jr.'s profile photo
 
That.map pattern is interesting
Add a comment...

Andrew Roberts

Tips & tricks  - 
 
const declaration

Now here's a fairly obscure question: does anyone use the const declaration in Apps Script?

It works for server-side script:

function test_const() {
const A = 1
var A = 2 // TypeError: redeclaration of const A.
}

but Google discourage it's use for client-side as it wasn't supported by IE until v11/Win8.1 (surprise, surprise). You may well be using your constants client-side so they say use @const.

https://google.github.io/styleguide/javascriptguide.xml#Constants

So I guess we are best sticking with @const.

Although of course we are all putting all our code through jshint.com which catches re-declaration of const's anyway :)
1
Bruce Mcpherson's profile photo
7 comments
 
I didn't know de.structuring was partially supported... Thx.
Add a comment...

Alan Wells

Tips & tricks  - 
 
If you want to pick files from the Google Drive, and don't want to use the Picker API, here is an Apps Script Web App with the beginnings of a file picker.  Currently, it looks up spreadsheet files, but you can modify it for any type or all types of files.  I've experienced issues with the Picker API and so have other people.  So, I created my own picker.  If the user is already authorizing permissions to Drive, then you don't need OAuth with this picker.

https://drive.google.com/open?id=1RMv8Roxy33TB7Ap7GrX7W45uAKRLnkcCNSpUY_nMpUTuYatWXKqJX2xq

In my Add-on, the Add-on needs access to Google Drive, so there is no need for using OAuth2 to get a list of files.

The best thing to do, is make a copy of the file, and run it from your own drive.
4
Riël Notermans's profile photoAlan Wells's profile photo
2 comments
 
I had an issue with the Picker showing both Sheets and Form files when I explicitly designated only spreadsheet files.  I tried for a long time to make it work.  I have no idea what the problem is.  All I can do is make wild guesses about what conditions caused the problem.  I also had problems with the formatting.  Trying to get the scroll bars to not overlap was a nightmare.  I never did get that fixed.  At some point, I just gave up.  Another issue is that the Picker requires going through the process of registering your App, which I don't need to do anymore if the user is giving permission to Drive, which they will always need to do if I need to create a spreadsheet.  So, the chances are, that if a picker is needed, the user needs to give permissions to Drive for some other reason.  There's no reason to pick a file that you'll never read or write to.  Meaning that, if the code has permission to Drive, why not just use that instead of an API.
   I'd like to be a compliant user, and do whatever I'm told to do, (And I've tried) but I had no other option.  This issue could segue into other topics about the tools available to developers and what that means for attracting developers and users.
   I found a solution that helps me continue to use Google products.  I want to use Google products, and I want users using my Add-on.  That's good for Google, and maybe someday it will be good for me.  I hope I'm not in fantasy land, deceiving myself.  My opinion is, that Google needs to put more resources into issues like this.  I have no idea who created the picker API, or what the benefit is to them, all I know is, that I think it has served me poorly.  And if Google and the developer of the API wants more users, then there needs to be improvements.  I don't want to be unfair or unappreciative, but either I had to come up with a solution, or abandoned Google.  I wasted a LOT of time on the picker.  Time I could have put into making my Add-on better.  Google and the developer of the API owe me nothing.  I haven't paid them anything.  I will just take whatever I can get.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Today's post could be useful if you use Slideshare (or want to get data about other's slideshares). It's an Apps Script library that gets slideshare data and stats using the sldeshare API. The example here just gets all my shared slides and populates a sheet with them.

#gde   #googleappsscript   #blogpost   #slideshare  
4
1
Bruce Mcpherson's profile photoMartin Hawksey's profile photo
2 comments
 
+Bruce Mcpherson missing some as well. Must dig out and upload my Google Wave talk ;)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
This actually started with a post by +Romain Vialard who reminded us that the new sheets v4 api could detect filters on a sheet. I've created a library here that mixes that api with apps script - so that with apps script , you can detect and get filtered values from a sheet. It would be nice to see this supported natively at some point, for now - well .. here's a workaround.
#gde   #googleappscript   #blogpost  
12
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
for any of you running excel and sheets... some collaboration using sheets V4 api.
 
The new sheets v4 api is designed to allow all the goodness of Google Sheets directly from other platforms.

So of course I couldn't resist creating a VBA api which uses it. This allows you to push or pull a sheet (or a number of sheets) between Excel and Sheets in just a line or two of VBA.

It handles Oauth2 directly from VBA too, so it couldn't be simpler.

#gde   #googleappsscript   #vba   #googleapis   #blogpost  
12
5
Marco Ferraresi's profile photoBruce Mcpherson's profile photo
6 comments
 
The ads are served up by Google Adsense so they should be clean. The site is google sites behind so it should also be clean. I haven't seen this on any other platform I have tried... iOS. Windows. Mac. Chrome book .ie.. Chrome.. Safari.... But I don't have a galaxy to test it with.. Anybody got a galaxy they could try it with.

Btw.. Do you get this on other pages in the same site ...? Www.mcpher.com .... Never seen anything like this before....
Add a comment...