Post is pinned.Post has attachment
Google Apps Script and G Suite Add-on Developers Meetup at Google Cloud Next

If you are lucky enough to be attending Google Cloud Next '18 in San Francisco as part of the event we are hosting a Google Apps Script and G Suite Add-on Developers Meetup on Wednesday 25 July @1pm in the Community Lounge at Next.

It's designed as an informal gathering of the Google Apps Script developer community, but we are also opening invitations to give 5 min lightning talks to anyone who wants to showcase some of their work. If you are attending Next and would like to contribute we've a talk sign-up form https://goo.gl/forms/XfwVcfH51hCodUPG3

Regardless of whether you want to talk looking forward to seeing some of you at Next.

Note: Unfortunately this meetup is only accessible to people attending Next. We plan to record/stream the event via +Totally Unscripted
Photo

I've discovered a situation where putting data into cache is actually bad. If the function that is putting the data into cache, is running multiple times simultaneously, then there may not be enough time for the data to be put into cache before the other instances of the function can use the cache.

Image that you have a scriptlet in two different HTML files, and the scriptlet is calling the same function. Both HTML files are being processed simultaneously. The same function is being run in two seperate instances. Neither instance of the function has time to put the data into cache before the other instance of the function runs. Your wasting time and resources putting the data into cache because it can't ever be used. Both functions are running at the same time. They are both putting data into cache at the same time. When both functions start running, there is nothing in cache, so they both just keep running, and both put the data into cache.

So, it's not only a waste of time because neither function has time to get the data out of cache, but they are both putting the exact same data into cache at the same time, for nothing.

There is a solution though, put the data into cache before the HTML template gets evaluated.

I'm not smart enough to figure this out by imagining all possible consequenses of the code running. The only way I stumbled accross this, is by reviewing my debug logging output. My code was running the block of code to put the data into cache multiple times, which didn't make any sense. The objective is to put the data into cache once, and then all other instance of the code gets the data from cache. And there is no point in putting data into cache, unless other functions or other instances of the same function are accessing the same data. So, the timing of when you put the data into cache is critical. You need to put the data into cache as soon as possible, and before the functions that need it, run.

So, again, I'm going to put the data into cache in the code that opens the dialog box, before the HTML is evaluated. Basically, as soon as the user clicks the button to open a dialog box, put the data into cache before the template is evaluated.

The strategy I've always used before, is that if I'm going to get a specific value out of the data, that I might as well put ALL the data into cache at the same time, but that isn't going to work. I need to first get all the data out and put it into cache without looking up a specific value.

Can someone write the script for the following task in Google Sheets?

Every 24 hours, add cell A2 to cell A3, and put the sum in cell A3.

I want to take my Google Sheet from last school year that had all my students usernames and passwords and put that into a new sheet that coordinates with the students teachers from this year. It will save me a lot of typing or copy and pasting. Is there an add on that will allow me to simply type the teachers name for this school year next to the students name in the sheet from last year and have it filter the info into the proper sheet for this year?
I tried SheetGo, but I could not find how to filter the sheet to filter names to new teachers.

I am using this code to import JSON into google sheets. It will run up to 4 times before it completely fails. The data pulls in but only has a 3 out of 4 success rate from the URL API we are connecting to. Can anyone see an issue with the code below?

GitHub seems to be a good source, but I don't know what to make of it and am not a code writer myself. https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs Thanks!

function IMPORTJSON(url,xpath,runCount){
// IMPORTJSON receives 3 parameters:
// url: the URL of the website from which to retrieve data
// xpath: the specific query used to instruct the website which data to retrieve
// runCount: the retry count of this pass
// The function is set to run recursively UP TO 3 times (0, 1, 2) before it returns an error message for the retrieval.
if (runCount<3) {
try{
// /rates/EUR
var res = UrlFetchApp.fetch(url);
// Logger.log(res);
var content = res.getContentText();
// Logger.log("content: "+content);
var json = JSON.parse(content);

var patharray = xpath.split("/");
Logger.log(patharray);
Logger.log(patharray.length);
// Logger.log(JSON.stringify(json));
for(var i=0;i<patharray.length;i++){
Logger.log("json[patharray["+i+"]]: "+json[patharray[i]]);
json = json[patharray[i]];
}

if(typeof(json) === "undefined"){
return "Node Not Available";
} else if(typeof(json) === "object"){
var tempArr = [];

for(var obj in json){
tempArr.push([obj,json[obj]]);
}
return tempArr;
} else if(typeof(json) !== "object") {
return json;
}
}
catch(err){
runCount++;
if (runCount>=4) {
return "Error getting data";
} else {
Utilities.sleep(5000);
IMPORTJSON(url,xpath,runCount);
}
}
} else {
return "Error getting data";
}

}

Post has attachment
If you missed the +Totally Unscripted App Maker Q&A we did recently with +James Ferreira here's a recording with a summary of the questions:

0:02:17 Who has access to App Maker?
0:05:34 What is the difference between App Maker and HTML Service?
0:09:25 Domain restrictions when using App Maker
0:11:44 How complicated can you get with App Maker without writing any code?
0:14:25 Do you have to use Google Cloud SQL with App Maker?
0:16:39 How much does it cost to run Cloud SQL for App Maker?
0:19:40 Opportunities for Edu partnering to use App Maker
0:20:48 What resources are available to get started with App Maker?
0:26:15 Where to find out more about managing SQL databases?
0:29:10 What other integrations does App Maker have with other Google products?
0:30:12 Can App Maker be used offline?
0:31:26 Does App Maker have a way of letting users pick files from Drive/Team Drive?
0:32:40 Can you use existing Google Apps Script code in App Maker?
0:34:49 Can App Maker be setup to me mobile friendly?
0:37:39 Is there a particular forum to get support for developing with App Maker?

Hi,
I get this error this error message Missing : after property ID. (line 476, file "Code")
I have the following code.

var sname= sheet.getName() ;
if (sname in {"VNIR","CC","DWG","MW","WG","IR","UV"}) {

for (var row in values) {

if ( row > 0)
{
totaldata.push(values[row]);
}
}
}

Any help.
GlanPlon

I am trying to create a timestamp when the cell gets highlighted with a color. To give you a scenario, I created a doc when a newspaper gets read, the reader will highlight a cell in their personal color. Can that be done?

Hello,
I"m trying to get a script to move a list of files that have the clients names at the beginning of the file name separated by a ,(comma) in a folder to that respective client's folder(also in the same rout folder) and if the folder is not present than create such folder.


For example:
File name:
GABRIEL SMITH, 2018 LOG, 3025
GABRIEL SMITH, 2017 LOG, 3009
JOHN WORTH, CALCULATIONS, 2018
GABRIEL SMITH, 2016 LOG, 3005

FOLDERS:
JOHN WORTH (one file goes to this folder)
GABRIEL SMITH (all three GABRIEL SMITH files go on to this folder)

is this possible?

Post has attachment
Hello!

I am trying to create a script which will copy all rows in Sheet1 which have the value "Walmart" in column P into Sheet2.

https://docs.google.com/spreadsheets/d/1ljzNxWGwA0rUYFRb0lObVuaXMsRLiBAa1O0WCkI4cgE/edit?usp=sharing

I tried using some sample script I found online but couldn't get them to work. Can anyone please help give me a starting point?

Thanks!
Wait while more posts are being loaded