Stream

Join this community to post or 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
4
Add a comment...

Alan Wells

Tips & tricks  - 
 
I just implemented a very basic version of the Ace Code editor inside of a stand alone Apps Script Web App.  It's default is for a dark theme and JavaScript.  The syntax folding is even working.  One major problem is, that there is no menu, or way to save the content.  But I got something basic working in about 3 hours.  I copied a huge file into it, 19,000 lines, and it was instantaneous.  With the Apps Script code editor it would have taken 2 minutes for the pasted content to show up.  Ace is open source which I like.  I'd really like to get some features added, like a file sidebar, and a way to add code snippets with just the click of a button.  If anyone is interested in seeing how I did it, or wants to contribute, here is the Apps Script file open to the public

https://script.google.com/d/1nl2mbXvNijFePxdEcRyZRTCZJBRjVZKNtZivQgFRSBSds2FV16LXDvCB/edit?usp=sharing

To see the results, you will need to make a copy of the file, and publish it as a Web App.
6
Riël Notermans (Zzapps)'s profile photoAlan Wells's profile photo
8 comments
 
I'll probably share the file, and post the Web App on Git Hub at some point.
Add a comment...

IBOAPP Support DE/AT

Tips & tricks  - 
 
Debugging with Source Trace
add it to your log function

This will not work for every kind of error, but some. We use it as an option of the Log class. So you may need to find a way to integrate it into your development procedure if you are still looking for ways to improve debugging. If you are not familiar with interpreting the error object and using ScriptApp.getResource, just leave a comment. We will take time tomorrow to extract additional layers from our current implementation.

function getSourceTrace( source, key, row ) { //+ HMK 20160214
var t = source[row] || '';
do {
row--;
t = row+1 +' ' + source[row] + '<BR>' + t;
} while ( source[row] && source[row].indexOf( key+'.' ) != 0 && source[row].indexOf( 'function ' + key ) != 0 );
return t;
}

function getKeyRowList( e ) { //+ HMK 20160214
var kr = [];
e.split('at').forEach( function ( r ) {
r = _.trim(r);
if ( r && r != '<BR>' && r.split(':').length > 1 ) {
kr.push( r.split(' ')[0].split('<BR>')[0].split(':') );
}
} )
return kr;
}
1
1
iBO App Service's profile photo
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you are writing Add-ons that you want to run on both Apps Script and the Office JavaScript API you need to give some thought to how you can just 'write them once', since the APIS are fairly different

One way is to create Polyfills that make one look a bit like the other. Here's the first of a few I'll be publishing.

#gde   #googleappsscript   #javascript  #office #blogpost
4
1
김진달's profile photo
Add a comment...

Jonathon Broughton

Tips & tricks  - 
 
Too much time wasted today not realising the emailsettingsAPI doesn't work with service accounts no matter who auth'd them or what scopes apply.

Supers User only. 
4
1
iBO App Service's profile photo
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I posted a neat way of watching for changes on the server from the client htmlservice code yesterday.

The reason I put this together, is that I'm making an Office 365 version of my SankeySnip add-on (which is somewhere in the depths of the add-on publishing process at the moment).

One of the cool things about the Microsoft Office API is that it has binding built in. This means that you don't need to do anything but wait to be told that there is a change in the hosting sheet. I wanted to simulate that in the Sankey Snip code so that the structure would be exactly the same for each of the Office and Apps Script versions (which doesn't have binding).

I just applied this technique to the Apps Script version, and the entire code for polling the server from the client and updating the chart if there are any changes now simply boils down to this.

client.start = function () {
watcher.watch(function (current) {
// data has changed .. go and update the chart
Process.syncResult (current);
});
};

It's super simple to use. If you have things like this to do then try out and let me know how you get on.
http://ramblings.mcpher.com/Home/excelquirks/gassnips/gaswatch

At the moment it watches for data changes, selection changes and changes in the number or names of sheets in the workbook. Its real easy to add other things so let me know if you have an idea for something else to watch.

http://ramblings.mcpher.com/Home/excelquirks/addons/sankeyaddon

#gde #googleappsscript #blogpost
Liberate Excel data from the desktop
9
4
Bruce Mcpherson's profile photoMichael ONeal's profile photoiBO App Service's profile photoStephen Mattison's profile photo
2 comments
 
+Steve Webster actually the MS API has just been greatly enhanced , so that's why I'm resurrecting my interest. I ported a previous version over to office but it was a bit of a hack,
http://ramblings.mcpher.com/Home/excelquirks/addons/officeaddins
so Im doing it again using the newer API..

I also have an entire chapter in my book (using the old api), implementing the same add-on in each platform if you need to get started on this.
https://library.oreilly.com/book/0636920045816/going-gas/search?ref=search&q=%22...but%20different%22%20javascript%20api%20maps
Add a comment...

Remate Martinez

Tips & tricks  - 
 
hello good morning , ask !!!!
This command I 'm doing , I need data that provides me , continue adding down, but not how to do it , here I unfurled them what I've done.
if you performed but not continuous down if not erasing my previous and repositions in the same place , from as much and more than grateful thanks

function MULTIPLOS() {
var hoja1 = SpreadsheetApp.getActiveSpreadsheet();
var hoja2 = SpreadsheetApp.getActiveSpreadsheet();

var valor1 = hoja1.getRange('M2').getValue();
var valor2 = hoja1.getRange('M3').getValue();
var valor3 = hoja1.getRange('M4').getValue();
var valor4 = hoja1.getRange('M5').getValue();

var lote = valor1
var cantidad = valor2
var precio = valor3
var comprador = valor4


hoja1.getRange('k13').setValue(lote)
hoja1.getRange('l13').setValue(cantidad)
hoja1.getRange('m13').setValue(precio)
hoja1.getRange('n13').setValue(comprador)
 ·  Translate
1
Alan Wells's profile photoRemate Martinez's profile photo
6 comments
 
i think that's true!
Add a comment...

Riël Notermans
moderator

Tips & tricks  - 
 
Unfortunatedly ServiceWorkers are not working from GAS, but maybe the Apps Script team can add a service worker to the HtmlService output to be available client side, and then do all the awesome stuff.

If you are interested, give it a star :)

https://code.google.com/p/google-apps-script-issues/issues/detail?id=6042

For the Apps Script team: ofcourse I am not sure if it is technically impossible or if there are other workarounds for the redirect.
8
Elliott Shafii's profile photoEric Koleda's profile photo
2 comments
 
A little late to this thread, but closing the loop, the issue is that service worker scripts have a lot of restrictions on how they are served, and Apps Script currently can't serve those scripts in a compatible way: https://code.google.com/p/google-apps-script-issues/issues/detail?id=4674
Add a comment...

Flemming Steffensen

Tips & tricks  - 
 
Newbie question.

I´m attempting to use the map function to optimize my function on ranges, as described here: https://developers.google.com/apps-script/guides/sheets/functions#optimization

I'll use the example code from that site to illustrate my problem:
============
function DOUBLE(input) {
if (input.map) { // Test whether input is an array.
return input.map(DOUBLE); // Recurse over array if so.
} else {
return input * 2;
}
}
============

The above works wonderfully, but the formula I now work on needs the size of the range as part of the calculation:
============
function PROBLEM(input) {
var N = input.length;
if (input.map) { // Test whether input is an array.
return input.map(PROBLEM); // Recurse over array if so.
} else {
return input * N;
}
}
============

The problem is that N is undefined at the time of calculation, as the calculations are not performed on an array but on a value.

And sure it makes sense that it fails, and I'll have to rewrite it using loops, but it made me think if there are a method to overcome this problem?

In other words: Is it possible to set a constant value inside a function called with the map function?
1
Bruce Mcpherson's profile photoFlemming Steffensen's profile photo
10 comments
 
Thanks. Looks like just the right book for me. :-)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
By using the Execution API you can abstract away accessing BigQuery to one central script. In this example, you'll see how to use the JSON API to access BigQuery, and control it from a different project via the Execution API.

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

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Most of the Oauth2 material I've written about covers how to do manage that process in an Apps Script Webapp, or using a service account. If you want to do a user based auth dialog from a sidebar, there are a couple of minor changes needed to the process, but it's still pretty straightforward. Here's how to do it.

#gde   #googleappsscript   #blogpost  
2
1
iBO App Service's profile photo
Add a comment...

Riël Notermans
moderator

Tips & tricks  - 
 
So, want to learn regexes?

This is done quite nice !
Happy puzzling!
A crossword puzzle game using regular expressions. Earn achievements completing puzzle challenges. Easy tutorials for people new to regular expressions.
15
4
iBO App Service's profile photoBruce Mcpherson's profile photoJasper Cuvelier's profile photoStephen Mattison's profile photo
2 comments
 
Man this is really using up my time...
Add a comment...

Riël Notermans
moderator

Tips & tricks  - 
 
Exiting stuff. At last: new Google Sites are coming!
21
2
Kathryn Marie's profile photoFaustino Rodriguez's profile photoMartin Hawksey's profile photoLaura Gibbs's profile photo
11 comments
 
Applied already, long awaited update
Add a comment...

Anas Penwala

Tips & tricks  - 
 
Hi Guys,

Hoping someone could give me some suggestions regarding this.

I need to create a break tracker for my team using Google Apps wherein I can start the timer when I go on a break and then stop it when I am back... It should record the time which cannot be changed by the TeamMates and only the admin should have access to it. Apart from that, after we press the end button, it should be recorded in a form or a sheet making it easier to track.

Is there a guide for it?

Any help would be appreciated.

Thanks!
1
Alan Wells's profile photoAnas Penwala's profile photo
6 comments
 
Okay! What inspires you?
Add a comment...

Riël Notermans
moderator

Tips & tricks  - 
 
Lots of fixes today !! :)
Google Apps Script issues and feature requests.
13
Zig Mandel's profile photo
 
yeah 2758 finally
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's a nice way of detecting and managing data and selection made server side from the client. Very useful if you have an add-on that needs to reflect changes in the sheet data right away.
#gde   #googleappsscript   #blogpost  
6
2
Bruce Mcpherson's profile photoLee Garvey's profile photo
Add a comment...

Christian Torres M

Tips & tricks  - 
 
I was studying this exmple and have one doubt about showError function. In this part:
$(function() {
$('#run-translation').click(runTranslation);
$('#insert-text').click(insertText);
google.script.run.withSuccessHandler(loadPreferences)
.withFailureHandler(showError).getPreferences();
});

If I get an error where would the error message? Taking in account how is defined that function(showError). In this case this willl receive only one parameter (error msg) or not? Then what element would take for displaying the error?
https://github.com/google/google-apps-script-samples/blob/master/translate/Sidebar.js.html
1
Bruce Mcpherson's profile photo
 
Withuserobject is passing the element that was clicked. It will be passed as the secmd argument to showerror which creates another element appended to the clicked object to show the error.

I don't really like this approach and I'd rather use a fixed element for errors that gets hidden.showed as required.
Add a comment...

Remate Martinez

Tips & tricks  - 
 
hola buenos dias, pregunta!!!!
de este comando que estoy realizando, necesito que los datos que me brinda, continuen agregando hacia abajo, pero no se como realizarlo, aqui les desplego lo que he hecho.
si lo realiza pero no continua hacia abajo si no que me borra el anterior y lo vuelve a posicionar en el mismo lugar, desde ya mucha gracias y mas que agradecido


function MULTIPLOS() {
var hoja1 = SpreadsheetApp.getActiveSpreadsheet();
var hoja2 = SpreadsheetApp.getActiveSpreadsheet();

var valor1 = hoja1.getRange('M2').getValue();
var valor2 = hoja1.getRange('M3').getValue();
var valor3 = hoja1.getRange('M4').getValue();
var valor4 = hoja1.getRange('M5').getValue();

var lote = valor1
var cantidad = valor2
var precio = valor3
var comprador = valor4


hoja1.getRange('k13').setValue(lote)
hoja1.getRange('l13').setValue(cantidad)
hoja1.getRange('m13').setValue(precio)
hoja1.getRange('n13').setValue(comprador)
 ·  Translate
1
ALBERTO JAVIER FERRARA SAENZ's profile photo
10 comments
 
Por otro lado, si estas trabajando con el mismo libro y la misma hoja, no requieres declararlo dos veces... por lo que solo declara la "hoja1", en cuanto a getRange(number, number) prueba con números fijos y luego con los que te sugiero, primero tienes que ver que funcione...

Ejemplos tomados de google:
var range = sheet.getRange("A1:D10");

SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4)

sheet.getRange(1, 6)

uno como el que usas sería algo así como:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(4,5).setValue(Valor);

o incluso:
sheet.getRange("M"+LastRow).setValue(Valor);

 ·  Translate
Add a comment...

Ricardo Baldi

Tips & tricks  - 
 
How can I acces the row's "hide situation" in a sheet? This "property" exists?
1
Alan Wells's profile photo

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I read the announcement today that Bigquery was integrated with Sheets, so of course I couldn't resist using Bigquery SQL to access sheets directly from Apps Script.
so ....
..link a bigquery project with a sheet
..access that project from apps script (im using a service account so it can easily be shared across many projects)
..run sql from apps script to access your sheets

It's pretty cool.

#gde   #googleappsscript   #blogpost   #bigquery  
5
Anees Hameed's profile photoBruce Mcpherson's profile photo
2 comments
 
You can use it as federated input to a bigquery table, then query the table using the bigquery Ui, or via the bigquery advanced service is the bigquery json API. If you update the spreadsheet, the changes will be reflected in the attached bigquery table.
Add a comment...