Stream

Join this community to post or comment

Riël Notermans
moderator

Tips & tricks  - 
 
Using a Drive text-file to serve an object to your webapplication using JSONP

Sometimes you don't want to call a script to get JSONP data in your web-application. A reason could be you need specific access rights. Serving JSONP from a textfile is quite easy. This is how I do it, sending and object with data to a webapplication:

In GAS, the following code writes a JSONP object to a textfile from any JS object in gas:
 
  var file = DriveApp.createFile(name, "")
  var stringified = JSON.stringify(obj);
  file.setContent("myfunction("+stringified+")");  
  var id = file.getId()  //you need the ID later

Now, in your webapplication, the following code fetches the content and creates the object. You need jQeury:
 
  var URL = "https://drive.google.com/uc?export=download&id=YOURFILEID";
  $.ajax({
        url: URL,     
        jsonp: "myfunction",     
        dataType: "jsonp"      
   });
   
    function myfunction(obj) {
          console.log(obj);
    }


Very quick and easy. You can update the file with a time trigger or on any way you want.


PS if you have the file on a particular domain and you need to make sure the right account is used when logged in into multipla accounts, include the domain in the url:

https://drive.google.com/a/mydomain.com/uc?export=download&id=YOURFILEID 
4
Faustino Rodriguez's profile photoRiël Notermans's profile photo
5 comments
 
Drive export is limited somewhat, keep that in mind with many users.

You can host the file and do the same though :)
Add a comment...

BRTCo Developer

Tips & tricks  - 
 
I'm looking for a basic 'sample' script that can "load" an entire sheet (Lookup Table) into memory, and then selectively unload it into another sheet (with the ability to rearrange/omit original column values).  Can anyone point me in the right direction?
1
BRTCo Developer's profile photo
5 comments
 
Anyone have any ideas for this array handling problem?

I know that you can efficiently load an entire sheet into an array with a single read like...

Array=Sheet.getDataRange().getValues();

And you can unload the array with a loop like this...

for(var i = 0; i < Array.length; i++){              
    New_Sheet.appendRow(Array[i]);

But, is there a way to unload the entire array with a single write statement????
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's a useful Apps Script timing wrapper snippet  to check your functions relative performance.

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

Spencer Easton

Tips & tricks  - 
 
Here is a 'yet another' priority level logging library.  I wrote this as I was exploring deeper functional programming.  The results are a tiny yet extremely flexible priority level logging system.  The github repo has the library project key, source code , documentation as well as an example project.
Apps-Script-PriorityLogs-Library - A small library to add priority logging to apps script
5
Web Apps R&D's profile photo
 
Would you be interested in a formatLog and whereAmI function ? This is how the log could look like (we just write it to spreadsheets because it is easier to add additional functions

23.07.2015 10:38:26 (Timestamp)

-2332 (Duration)

 "Spreadsheet (DB) 507 (WhereAmI)

FolderIndex (BOX) 127 
FolderIndex (BOX) 122 
FolderIndex (BOX) 115 
FolderIndex (BOX) 84 
FolderIndex (BOX) 77 
Code 6 BOX_FolderIndex_getMarketFolder

" Spreadsheet.prototype.init formatLog (whatever the exception might be)
Add a comment...

Web Apps R&D

Tips & tricks  - 
 
Additional Source for help?
may be, but consider reading the comment made by +David Bingham 

Sometimes Stackoverflow or Github are not enough.

This community is close to 9.000 followers, well, may be we just have to spread the message to double the figures.
How can I generate a random whole number between two specified variables in Javascript, e.g. x = 4 and y = 8 would output any of 4, 5, 6, 7, 8?
5
David Bingham (Mogsdad)'s profile photoWeb Apps R&D's profile photo
8 comments
 
+David Bingham your comment makes sense. Let's agree to leave it as is. The subject of this post has been changed and references your comment. The comments by +Daniel Frost +Michael Ellis and +Spencer Easton just refer to the second part of the post. 
Add a comment...
 
David Bingham (Mogsdad) originally shared:
 
Converting from UiApp + Chart Service to Html Service + Google Visualization API

In July 2012 Roee Eilat and Eric Koleda, respectively members of the Google Charts and Apps Script Teams, published a popular tutorial: “Building a Charts Dashboard”. Later, in December 2014, Google deprecated the UI Service that supports Charts – while…
In July 2012 Roee Eilat and Eric Koleda, respectively members of the Google Charts and Apps Script Teams, published a popular tutorial: "Building a Charts Dashboard". Later, in December 2014, Googl...
11
2
Michael Ellis's profile photoDavid Bingham (Mogsdad)'s profile photoJose Luis Garcia's profile photoElena S.'s profile photo
5 comments
 
+Riël Notermans Yes, you'll get better performance that way, however the choice I made, and the point of the blog post, was very deliberate - separation the concerns of display from data storage makes the code more easily maintained. For instance, if I found that my data was too large for a spreadsheet, I would only change the (poorly named) getSpreadsheetData() function, and nothing in the UI would need to change.
Add a comment...

Daniel Frost

Tips & tricks  - 
 
How to use Google Apps Script Charts in your Web Application!
Using Google Charts in your Web Application     Today's hack will be a short tutorial on how to use Google Apps Script Charts in your web application. It's kind of a continuation of my last installment on how to use images ...
6
2
Daniel Frost's profile photoJanek Wagner's profile photoPéter Erben's profile photo
 
Sorry with problems with the readability and images covering text, I adjusted everything.
Add a comment...

Edouard Brasier

Tips & tricks  - 
 
Browser performance.
Hello, it seems that my google apps script is running much faster using the vivaldi browser on my machine. 
1/Vivaldi
2/Chrome
3/FF
4/IE

It could come from other factors. Are you experiencing the same?
According to your experience what is the fastest browser for GAS?
33 votes  -  votes visible to Public
Chrome
85%
IE
0%
FF
3%
Vivaldi
9%
Microsoft Edge
3%
1
Bruce Mcpherson's profile photoEdouard Brasier's profile photo
2 comments
 
Yes my test is with the iframe mode htmlservice and jquery. Performance varies very significantly between browser. I really like the feel of vivaldi.
Add a comment...

Paul Swanson

Tips & tricks  - 
 
Just updated a script of mine - it sends out an automatic email to people who are filling out a Google Form with a summary of their response and also a link to be able to go back and re-Edit their response.

For details, check out my post:
http://www.teacherpaul.org/3327
7
1
Andreas Hofer's profile photoZac Garcia's profile photo김진달's profile photo
4 comments
 
Hi +Andreas Hofer ! Right now, there's no way to redirect a user outside of the Google Form using Google Apps Script. Currently, FormApp only lets you edit the form's formatting and internal flow (along with form responses). There are a couple of options for you, though:
1. Use a script to email the respondent a link to the site you want them to visit
2. Copy the HTML of the form into an HTML Service web app, and use Javascript to redirect the user to the new web page. I'm not sure if this second option will work if you are redirecting to a third party domain.
Add a comment...

Alan Wells

Tips & tricks  - 
 
For debugging HTML, putting a function into the browsers window object seems to allow the "debugger;" line to display the client side JavaScript in the debugger.
For example:

function myFunction() {};

Doesn't seem to allow "debugger;" to display the JavaScript.  But put the function into the window object like this:

window.myFunction = function() {};

Seems to display the code as written when the debugger halts the code.
1
Riël Notermans's profile photoA. Luca B's profile photo
2 comments
 
interesting

will try
Add a comment...

Ivan Kutil
owner

Tips & tricks  - 
 
Tool to pack Node.js module files and module system emulator within one source file,that is executable on Google Apps Script environment

https://github.com/tyskdm/codegs
codegs - Tool to pack Node.js module files and module system emulator within one source file.
6
1
Web Apps R&D's profile photo
Add a comment...

Benjamin Reich

Tips & tricks  - 
 
Thought I would cross-post this.  

For anyone experiencing the IFRAME issue where a useless y scrollbar appears in Webkit browsers, you can work around it by adding the following line of javascript to any html file or to any external javascript file which may be used as a startup library:

  parent.document.getElementById('userHtmlFrame').style.overflowY = "hidden";

You may see the scrollbar briefly before it disappears.
3
Riël Notermans's profile photo
 
Check in the box. You just finished a customers biglist here ;)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
You may not have realized how much processing time is wasted by filling down formulas rather than using array formulas in Sheets. Here's an analysis that should convince you to always array-enable custom formulas 

#gde   #googleappsscript   #blogpost  
8
1
Michael Ellis's profile photo김진달's profile photo
 
Great detailed explanation.  
Add a comment...
 
David Bingham (Mogsdad) originally shared:
 
Did you know - log to spreadsheet from client JavaScript

Google Apps Script has a Logger class, intended to help debug by enabling you to log the operation of your code, and examine run-time data. Viewing those logs is supported through the View > Logs menu option in the Google Apps Script editor.…
9
1
Michael ONeal's profile photo
Add a comment...

Saurabh Sharma

Tips & tricks  - 
 
Google today launched a new plug-in for Microsoft Office that gives you access to all of your Word, Excel and PowerPoint documents in Google Drive right from Microsoft’s desktop apps.
4
1
Algirdas N's profile photo
Add a comment...

Martin Hawksey
moderator

Tips & tricks  - 
 
Interesting workaround for getting filtered values from Google Sheet 

See comment #c79  in this issue ticket

https://code.google.com/p/google-apps-script-issues/issues/detail?id=524#c79
Google Apps Script issues and feature requests
4
2
Gunther Keim's profile photoNishant Singh's profile photo
Add a comment...
 
David Bingham (Mogsdad) originally shared:
 
Did you know? (Custom Functions in Google Apps Script)

In Google Apps Script, Custom Functions are special. They have limitations that other script functions don’t, but to compensate, they have been given awesome powers! Ok, that’s a bit strong – let’s say they have unique capabilities that aren’t immediately…
12
2
Daniel Frost's profile photo김진달's profile photoIstván Maczkó's profile photo
 
Nice
Add a comment...

Daniel Frost

Tips & tricks  - 
 
Full write up on how to include images in your Google Apps Script Web Application directly from your google drive, or google apps script project. Hope this helps.
9
2
Faustino Rodriguez's profile photoDaniel Frost's profile photoFilipe Gazzinelli L F Werneck's profile photoGunther Keim's profile photo
3 comments
Add a comment...

Web Apps R&D

Tips & tricks  - 
 
What's the use of SpreadsheetApp.flush()?
Lesson learned the hard way

In order to exchange values between to parallel processes, running in different accounts, we used a shared spreadsheet. But in order for a value to "arrive" it needs to be "flushed".

The sender must e.g.

s.getRange('A1').setValue( 'for receiver'); // changes the value
SpreadsheetApp.flush();

and the receiver must e.g.

SpreadsheetApp.flush();
s.getRange('A1').getValue();

In order to get the change. 

Without SpreadsheetApp.flush() the change of the value will not be reliably determined. 
10
3
Riël Notermans's profile photoIgnacio Baixauli's profile photoAlexander Ivanov's profile photoAlex Vomore's profile photo
4 comments
 
Also counts for DocumentApp.saveAndClose().
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Just a tiny simple but handy snippet today (it's too hot for more...)
 
Ever had to find the value of a property in an object given a string representing its full key.?

for example, given the string  'properties.key', how to find object.properties.key - 

here's how to use array.reduce() to do it.

'properties.key'.split(".").reduce(function(p, c) {
     return p[c];
   }, {
     properties: {
       key: 'i am the key',
       name: 'the name'
     }
   });
5
Add a comment...