Stream

Join this community to post or comment

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
Here's a simple library to access Firebase JSON API from Apps Script.(server side) . It can also return Promises rather than results if you prefer. I'm using it to manage player data for the big quiz bigQuery app. I had originally planned to use Google Play Game Services for that, but it was just way too laborious to get going (as an aside I think Google have got this game play api very wrong). Using Firebase instead is a breeze.

#gde  #googleappsscript #blogpost
7
Bruce Mcpherson's profile photoFaustino Rodriguez's profile photo
5 comments
 
agreed +Bruce Mcpherson , we better forget about a DbAbs driver for FB
Add a comment...

Spencer Easton
moderator

Tips & tricks  - 
 
Here is a sample project on how to setup and use  reCAPTCHA in a WebApp.  Check out the setup instructions in the the repos README.
Apps-Script-reCAPTCHA-sample - A sample project showing how to use google reCaptcha in a WebApp
9
Holger Hubbs's profile photoFaustino Rodriguez's profile photo
4 comments
 
thanks +Spencer Easton, I didn't even know such Google reCAPTCHA tool was available for the rest of us
Add a comment...

Alan Wells

Tips & tricks  - 
 
It looks like the function that is used for the "On Form Submit" event for a Google Form may run it's own instance of the function for every function invocation (Form Submission).  I wrote a script that makes many form submissions in rapid succession:

function testFormSubmissions() {
  var myFormID = '123_MyForm_ID';
  var submitURL = "https://docs.google.com/forms/d/" + myFormID + "/formResponse";

  var payload = {
    "entry_123_My_Item_ID":"Option 2",
    "entry_123_My_Item_ID":"Option 2"
  };

  var options = {"method":"post","payload":payload};

  for (var i=0;i<5;i+=1) {
    UrlFetchApp.fetch(submitURL, options);
  };
};

That function makes 5 form submissions in rapid succession.  I created a test Form, that handles the "On Form Submit" event:

function testOnFormSubmit(e) {
 
  var ssForLog = '123_My_Sheet_File_ID';//Enter a file ID of a spreadsheet file here to log data to that spreadsheet.
  SpreadsheetApp.openById(ssForLog).getSheetByName('Sheet1').appendRow(['stackFnc Ran', e.response.getId()]);
  Utilities.sleep(10000);
};

Note the line:
    Utilities.sleep(10000); //Pause for 10 seconds

When I observe the spreadsheet, all the form response ID's get written to the spreadsheet without any delay, there is NO 10 second delay between any of the spreadsheet append actions.

So, every "On Submit" function invocation must be running independently.

That's great.  That's good news.  Now, I'd be curious to know, what happens with functions that are called from the "On Submit" function?  Would they all run independently?  If so, I'd think that every function invocation of the same function name would maintain it's own variable values.  For a function that is NOT an On Form submit function, it doesn't seem that the function is maintaining it's own "session" of variable values.

I've made multiple "google.script.run.myFunction()" calls from a Web App, and the the values seems to get overwritten, if multiple function calls are made in rapid succession.  So, I'm assuming that the "On Form Submit" function is being handled differently than a "google.script.run.myFunction()" call?
5
A. Luca B's profile photoAlan Wells's profile photo
8 comments
 
Well, I think it can be solved.  But, ultimately, code either works or it doesn't.  So, in that sense it won't matter what either of us think.  ;)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
Here's how to use Promises for making server side calls from Apps Script HtmlService. (If you do that, then you really want to do this).

#gde   #googleappsscript   #blogpost  
4
1
Bruce Mcpherson's profile photoFilipe Gazzinelli L F Werneck's profile photo
5 comments
 
Updated post to show an example of whitelisting..
Add a comment...

Dennis G Daniels

Tips & tricks  - 
 
Interesting read. There are some small typos in the text but the author provides sample code and a sheet to test it yourself.
Introduction Five years ago, I added a blog entry on how and why to use named ranges in Google Sheets, see Named Ranges. GAS provides Spreadsheet object methods to get Range objects using the range name and to set names for...
3
Add a comment...

Alexander Ivanov

Tips & tricks  - 
 
There is my function to check a file exist by id.
https://gist.github.com/oshliaer/a3fd6003a6eae45fd6856cf221cffd52
Does someone have other creative ways?
1
Yuriy Klyuch's profile photoAlexander Ivanov's profile photo
5 comments
 
+Yuriy Klyuch , you're right. But if my function returns just the true it would be too expensive function. At the same time, I need a function with a clear name. 
Probably, I incorrectly formulated the model of development, and this function is useless.

+Michael Ellis , thanks.
Add a comment...

Alan Wells

Tips & tricks  - 
 
JavaScript_Custom_Stack

Here is a link to a shared, stand alone Apps Script file:

https://script.google.com/d/193zAKLOI1l5buCXB8ypRbVnLwdVqYw3htcDe4LebKRkNOcHr78lmyO8l/edit?usp=sharing

What this code does:

"Stacks" incoming function invocations that will all run independently.

This project is meant to be an example.  It is set up to run in an Apps Script stand alone file, that is published as a Web App

  This is a fully functional Web App, but you must make a copy for yourself, and then publish it to use it.
  This Web App writes data to a spreadsheet.  The data written to the spreadsheet proves a point.  It
  shows that multiple functions are all running independently at the same time.

This web app is meant to be the first step in processing multiple Google Form submissions that come in simultaneously, or in rapid succession.

I wanted to separate out this part, from the part that will process the form submissions.  This code is short and simple.  It's not to hard to understand for someone who develops apps with Apps Script.

You should read all the notes in the GS_Main file.
4
Alan Wells's profile photoRiël Notermans's profile photo
6 comments
 
Playing around and discovering things that work less is just as much fun or valuable as always finding stuff that works ;)
Add a comment...

Martin Hawksey
moderator

Tips & tricks  - 
 
Here's a Google Analytics/Google Apps Script posts looking at sending in batch and queue time to reduce urlfetch and runtime (and having fun with a discussion data API)
 
You may think Google Analytics can be just used in sites where you can include tracking code but actually you can use GA to process other data. In this post I show how you can use Google Apps Script with the Canvas LMS API to push discussion data to GA

#GDE #GoogleAppsScript #Blogpost #GoogleAnalytics
Integrating third party APIs asynchronously with Google Analytics and batch sending tracking data whilst preserving the original activity timestamp
4
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
10
3
Bruce Mcpherson's profile photoJose Luis Garcia's profile photoBernhard Nimmervoll's profile photoOleg Moskalensky's profile photo
6 comments
 
http://shop.oreilly.com/product/0636920048503.do. +Michael ONeal looks like it's out on Oreilly shop now. 
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you have an htmlservice app being accessed 'as the user', you may want to to personalize that with the People API - here's an example.

http://ramblings.mcpher.com/Home/excelquirks/bigapps/bigquiz/peopleapi

#gde #googleappsscript #blogpost
8
1
Bruce Mcpherson's profile photo
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
Here's an apps script quiz webapp built from a file of more or less all the jeopardy questions ever asked, that I found on Reddit. Give it a go and let me know what you think. The link is in the post. It uses loads of Apps Script/Google goodies such as bigQuery , firebase, execution api, multiple oauth flows, material design, css animation.. and loads more. I'll write up how its built when I get round to it. Hopefully you'll find some useful bits and pieces for your app.

#gde   #googleappsscript   #blogpost  
10
Bruce Mcpherson's profile photoAlexander Ivanov's profile photo
3 comments
 
+Bruce Mcpherson , I like MUI, but I'm using https://getmdl.io/ often. Thanks for the response.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Need a house style for all your Apps Script webapps, or to share client side code and markup across all your apps, or even share code between client and server side.
here's how..
#gde   #googleappsscript   #blogpost  
5
3
Danielle Scaramellino's profile photoBruce Mcpherson's profile photo김진달's profile photoMarcos Gomes's profile photo
Add a comment...

Alan Wells

Tips & tricks  - 
 
When the getTimestamp() method is used to get the timestamp of a Form response, it returns a date.  That date is converted to the timezone of the script timezone.  So, I wanted to know if that would affect which responses are returned using the "getResponses(timestamp)" method.  So I created some code, and ran it, then kept changing the script time zone.   Thankfully, the code always returns the same responses, regardless of the script time zone setting.  Which is a concern, because of users across lots of different time zones.  The code I'm working on depends on being able to get responses from a certain timestamp onward.
2
Aaron Burg's profile photo
 
Those darn dates! 
Add a comment...

Alan Wells

Tips & tricks  - 
 
The documentation for:

    getResponses(timestamp)

States:

Gets an array of all of the form's responses after a given date and time.

https://developers.google.com/apps-script/reference/forms/form#getresponsestimestamp

But my tests show that it also INCLUDES the response of the timestamp used in the parameter.  Which is good, but the documentation is wrong.
1
1
Spencer Easton's profile photoAndrew Hyland's profile photo
 
Any errors in documentation should be added to the issue-tracker. That's the best way for it to get fixed.
Add a comment...

Gerónimo Tutusaus

Tips & tricks  - 
 
I made a folder where I collated lot of free resources for Google Drive (spreadsheets, slides, scripts). I tried to be organised and neat with subfolders.

Hope it is helpful for everybody and if you have anything to share, I would love to include it. I believe there is a lack of a repositories of homemade solutions and guides.

https://drive.google.com/open?id=0B27DguM4UkOcbmFrbW9PLWFYY0k

All the best,
How can I use ARRAYFORMULA and SUM together to get a column with the sum of the proceeding rows. 15/2/14. Hoja de cálculo. How to manage multiple importrange. 19/11/15. Usuario desconocido. Hoja de cálculo. How to manage multiple importrange. 19/11/15. Hoja de cálculo. Mail Merge ...
11
1
Rudiger Wolf's profile photoDavid Tew's profile photoMike Mcginn's profile photo
9 comments
 
Thanks Gerónimo - a good asset.
Add a comment...

Frank Solomon

Tips & tricks  - 
 
I just published a new article at Bit Vectors. It extends earlier Bit Vectors articles, showing how to integrate file save / print features with Google Apps Script applications.
1
Add a comment...

Martin Hawksey
moderator

Tips & tricks  - 
 
Last November, Google opened up its in-house machine learning software TensorFlow, making the program that powers its translation services and photo analytics (among many other things) open-source...
10
3
Steve Webster's profile photoDavid Mullett's profile photo
Add a comment...

Alan Wells

Tips & tricks  - 
 
I'm using the Apps Script Color Chrome Extension from +Jean-Rémi Delteil but the BlackFox console color scheme makes the scroll bars very hard to see.  I figured out a way to change the scroll bar color in the Chrome Browser developer tools.  In the Apps Script code editor, right click on the scroll bar, choose "Inspect".  In the right hand window, with the "Styles" tab chosen, scroll down until you see the "scrollbar-thumb {" section.  You can change the scrollbar color right from a color picker in the dev tools.
3
Alan Wells's profile photo
 
Now if I could figure out how to access the content of the code editor, I could create a Chrome extension to change the scroll bar color.  (Or the developer who created Apps Script Color could do it.)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you have large files to write to Drive over 10mb - well, you can't - not using the apps script services anyway, and UrlFetchApp has a limit on it too, so it's not easy using the drive json api either. However, you can use resumable uploads to load the data in chunks. Here'a a library that takes care of all that nastiness for you - but watch you don't blow your daily Urlfetch quota.

#gde   #googleappsscript   #blogpost  
12
1
Faustino Rodriguez's profile photoBruce Mcpherson's profile photo
5 comments
 
Another hidden (in plain sight) gem, that I was overlooking
Thanks again
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
Today's video - Oauth2 for Apps Script webapps - published to run as the user accessing the script. This is very similar to the video on 'to run as the script owner', except that the token infrastructure is specific to each user.

The source for all these examples is now on github.
https://github.com/brucemcpherson/appsscriptvideos
More info at ..
http://ramblings.mcpher.com/Home/excelquirks/goa/walkthroughs

#gde #googleappsscript #video
4
Add a comment...