Stream

Join this community to post or comment

Hari Shankar Das

Tips & tricks  - 
 
An awesome trick to show a date picker in a blank cell....in case you are not aware of this....
(I did not know this before :P)

Step 1: Right Click on cell in which you want to enter a date.
Step 2: Choose Data Validation.
Step 3: Set Criteria to "Date" is a valid date.
Step 4: SAVE
Step 5: Double click on the cell and the date picker will pop-out.

Found here.
http://webapps.stackexchange.com/questions/17238/adding-a-datepicker-in-google-spreadsheet
7
Stéphane Giron's profile photoRiël Notermans's profile photo
4 comments
 
Oh a BLANK cell


Aaaaaah :)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
Todays tip: getting rid of dups in a JavaScript array.

You're probably familiar with the use of filter/indexof for getting rid of dups in a simple array...

var simple = [20,21,20,22,20];

// get rid of dups
Logger.log ( simple.filter(function(d,i,a) {
return a.indexOf(d) === i;
}));

result
[20.0, 21.0, 22.0]


Here's a way to do it if its an array of objects (or in the case of Apps Script spreadsheet data, it could be an array of rows of values.

// get rid of dups again
var harder = [
{id:20,name:"a"},
{id:21,name:"b"},
{id:20,name:"c"},
{id:22,name:"d"},
{id:20,name:"e"}
];
Logger.log ( harder.slice().reverse().filter(function(d,i,a) {
return !a.slice(i+1).some(function (e) {
return e.id === d.id;
});
}).reverse());

result -
[{name=a, id=20.0}, {name=b, id=21.0}, {name=d, id=22.0}]

if you don't care about which dup to keep, then just omit .slice().reverse() and .reverse()

#gde #googleappsscript #blogpost

7
Alexey Isachenko's profile photoBruce Mcpherson's profile photo
5 comments
 
yep.. that too!
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's a video tutorial on distributing copies of drive files using an apps script web app.

#gde #googleappsscript #video
14
2
Add a comment...

Anthony Osinski

Tips & tricks  - 
 
Anyone have any useful scripts that integrate Google Sheets, Google Keep and Google Calendar?
2
Spencer Easton's profile photo
 
Unfortunately there is no public Keep API. There is an open issue you can track:

https://code.google.com/p/google-apps-script-issues/issues/detail?id=3477
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
I'm almost finished recording my latest video course - "apps script for beginners", and thinking about how to distribute the course assets in as simple a way as possible - since they are mainly Apps Script source files, and the target audience is likely to be somewhat unfamiliar with Drive links, making copies of shared files, and github.

This Apps Script webapp will deliver copies of the contents of a folder to the users own Drive. That way, asset distribution becomes a link, one click exercise.

The code for the webapp is on github, but of course it will deliver a copy of itself directly to to your Drive with this link,
https://goo.gl/ML4CUi

#gde #googleappsscript #blogpost
4
Bruce Mcpherson's profile photo‫أكاديمية درسني (طريقك إلى التفوق)‬‎'s profile photo
11 comments
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Moving on from yesterday's post on Identifying which files in a Google Site are hosted in the soon (about 3 weeks from now) to be deprecated googledrive.com , the next step is to figure out how to reorganize all these required files into a directory structure appropriate for whichever method you choose to host them in the future.

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

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I'm looking through my site today to identify where I'm using googledrive hosting so I can fix it before it all goes away. The good thing about leaving things till the last minute is that the solution you eventually decide on is going to be the most up to date (+Spencer Easton will be covering this topic at this weeks Totally Unscripted broadcast). But the bad thing is that I have to do this...

753 pages in site
459 pages in site with hosting
2773 hosting changes need to be made

If you use Google Sites, and use Googledrive hosting with that, then here's a script to analyze and find all the changes you're going to need to make in the next 2 to 3 weeks.

#gde   #googleappsscript   #blogpost  
10
3
Bruce Mcpherson's profile photoRiël Notermans's profile photo
18 comments
 
But it is not clear for me if Storage can be used for hosting? 
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you're like me, then your Drive files and folders will be a mess, with copies of things in the wrong folders. But it's hard to track down and see what you have exactly. I've had a go at creating an Apps Script tool that analyzes Drive, looking for duplicates (you can describe what a duplicate is through parameters) and reporting on them. The code is open source, so it might make the basis for a useful add-on if anybody feels up to it.

#gde   #googleappsscript   #blogpost   #opensourcecode  
11
2
Manuel Serrenti's profile photoiBO App Service's profile photo
2 comments
 
:)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you use the cache or properties service, you've probably come across the problem where what you are trying to write is too big for the quota on the service. We;ll, you can easily compress your data first, and uncompress it on the way back - that way you'll fit 3 or 4 more times as much stuff in before breaking the quota. Here's how.

#gde   #googleappsscript   #blogpost  
3
2
Alan Wells's profile photoBruce Mcpherson's profile photo
4 comments
 
Alan.. To write to properties store, you would convert the bytes of the blob first into base64 as in the example. It's mainly only worth doing if you have, or could have, data that wouldn't fit in the target store .. Cache or property. 
Add a comment...

Anees Hameed

Tips & tricks  - 
 
#AddOn
In this community I have come across one same question a lot of times. The question is like, a user need to move/copy/delete a row data based on the value in a column. for eg: If at column F value is equal Approved then Copy data from range A to N to the sheet named Approved.

User may not be having a great idea of Google App Script,
And then one of the community member volunteers to write tailored code for the situation.
I though about generalizing these scenarios and created an Add-On for that, here it is 'Just Move It'.

Documentation:
https://sites.google.com/site/officeappstore/justmoveit
Writing your own rules and managing data was never easy before. Lets say, Hello JuMI
6
Spencer Easton's profile photoAnees Hameed's profile photo
8 comments
 
Ahh, that's cool...
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
A little while ago i released a library that showed a more functional approach to dealing with rows of data, like the kind you find in sheets. I must say that nowadays every time I touch a spreadsheet, I use this library as it saves a lot of time and mistakes.

I just added a couple of new methods to help deal with duplicate data.
getUniqueValues(columnName) returns all the unique values in a given column, and filterUnique (columnNames) reduces the data to get rid of rows that match on the given columnNames. Hope you find them useful

#gde   #blogpost   #googleappsscript  
12
Steve Webster's profile photoDavid Tew's profile photo
2 comments
 
Really very good.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Quite often people ask how to implement a client side progress bar that reports on server side activity progress. It's quite hard to do (especially a smooth one) because of all the asynchronicity of client server communication, as well as the synchronous nature of most server side services, but I thought I'd have a go anyway. I've updated my copyFolder webapp to incorporate this -

and of course you can it use it to get your own copy of the code
http://goo.gl/p3Ixbl

#gde   #googleappsscript  #blogpost
8
3
Afiq Rostam's profile photo
 
Too much effort 
Add a comment...

Dariusz Kuśnierek

Tips & tricks  - 
 
Hi, I just published a post about keeping track of Google Apps Script projects' total execution time. Please keep in mind I'm a coding beginner ;-)
About a month ago I started having some serious issues with my Google Apps Script projects. I was getting a lot of “Service using too much…
9
2
Oli Trussell's profile photoiBO App Service's profile photo
4 comments
 
+Dariusz Kuśnierek just keep in mind, +Amit Agarwal solution works for shallow loops only. Nested loops and recursion require a different approach, especially when the next step requires a consolidated state, i.e. the total consumption of compute time must be recorded and compared to a forecast in order to decide wether or not the next step can be processed within this cycle or has to be delayed into the next one.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's a quick tip for debugging htmlservice. This uses a deprecated <xmp> tag, but it still seems to work in Chrome anyway.

It can be hard to see what's finally being passed to html service , especially if the content is very long.

You can make htmlservice display the html markup as the webpage content by using the <xmp> tag round the stuff you want to to see .. like this example where i have a tricky bit of syntax to append and I couldn't easily see what was going wrong.

return HtmlService
.createTemplateFromFile('index')
.evaluate()
.append('<xmp><script>function getParameters() {'+
'return {folders:{target:"' +
e.parameter.target +
'",sourceid:"' +
e.parameter.sourceid +
'"}}};</script></xmp></body>');

That way my web page shows the code literally like this, so i can easily see any syntax errors.

<script>function getParameters() {return {folders:{target:"Gas for beginners - copy of assets",sourceid:"0B92ExLh4POiZVkd5Y2FVYmZmZjg"}}};</script>

#gde #googleappsscript #blogpost
2
Howard Burgess (H)'s profile photo
 
Cunning idea! I've used a similar thing where I watch the url used to call the webapp.

By appending a suitable complex parameter to the url (so someone can't easily guess it!), the content of the index.html file can detect that and include a script block that either turns on global debugging for my script files (where they have a lot of "if (window.debug === true) console.log(..) " statements already in place which suddenly start outputting) or it simply does a "<script>console.log(data.parameters...);</script>" to display in the console the passed in parameters. Sounds similar to your methodology and I can leave this in the production code as I know only the magic parameter will enable it at anytime (*)

(*) Of course, the web app also has other logic to ensure the debugging is only enabled by someone logged in who has an admin role, so it's quite safe to be in production code as well.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you've been following along with the steps for automating conversion of files on Sites from Drive hosting to some other method, here's stage 3. In stage 1 and 2 we identified where we were using drive hosting, and then proposed a new hosting structure for each of the affected files. This stage creates that hosting folder structure on drive, and copies over the files to it - ready for the next stage which will be to upload them to the chosen final hosting method.

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

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
A very short post showing a handy way of logging differences in strings in Apps Script.

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

Amit Agarwal

Tips & tricks  - 
 
Made with #AppsScript
 
How to make beautiful pixel paintings with Google Spreadsheets!
Learn how to create impressive pixel paintings with Google Spreadsheets. You can convert any image into pixel art with little effort.
10
1
Add a comment...

Hari Shankar Das

Tips & tricks  - 
11
Chris K's profile photoSimon Mackay's profile photo
2 comments
 
Not everyone can embrace modern ways of thinking +Chris K. I work like you.. 99% of my company refuse.. REFUSE! to work like us and DEMAND sometimes rudely.. for me to facilitate them the way +Hari Shankar Das has here. This is marvelous for me bringing peace to the world of my office. For now.. till i get all the users upgraded ;)

Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you are using Oauth2 with Apps Script, you probably need to do something to deal with the upcoming deprecatin of getProjectId(). Here's what you need to do.

#gde   #googleappsscript   #blogpost  
8
1
Joe Marino's profile photoBruce Mcpherson's profile photo
2 comments
 
It's marked as deprecated in the script app methods, but last time I looked, the examples hadn't been updated and it came kind of out of the blue.. I never noticed anything in advance about it in any case, although there might have been some announcement I missed. . Usually there's some kind of advance warning about stuff like this. It still works for now, and I haven't heard when it's going away. Maybe others have?
Add a comment...