Stream

Join this community to post or comment

Martin Hawksey
moderator

Tips & tricks  - 
 
Nice post from +Nico Miceli on getting Google Analytics data into Google Sheets
 
New Post!

If you have auto updating google analytics daily reports that are hitting sampling, pull it in batches! Check out my new post on how to do that with apps script.

http://nicomiceli.com/google-analytics-data-with-google-apps-script/

#gde #analytics #blogpost #googleanalytics

1
Add a comment...

M. Volgrand

Problem/Coding Help  - 
 

I'm getting the error code: "Invalid assignment left hand side, (line 1, file "Add notes")". I don't know how to fix this, here is my code and, below, an explanation of the variables.

"""
function newNote(cell, ans) {
var comment;
var a;
var b;
var c;
var d;

if (ans[0] == "Yes"){
a = 1;
}else{
a = 0;
}
if (ans[1] == "Yes"){
b = 1;
}else{
b = 0;
}
if (ans[2] == "Yes"){
c = 1;
}else{
c = 0;
}
if (ans[3] == "Yes"){
d = 1;
}else{
d = 0;
}

a.toString();
b.toString();
c.toString();
d.toString();

comment = "Air survey:" + a + "\n" +"Ground survey: " + b + "\n" + "Barnacles: " + c + "\n" + "Anomalies: " + d;
cell.setComment(comment);

if (d == "1"){
cell.setBackground("#ef00ff");
}else if (c == "1"){
cell.setBackground("#ff0000");
}else if (b == "1" && a = "1"){
cell.setBackground("#b47cff");
}else if (b == "1"){
cell.setBackground("#a8a18a");
}else if (a == "1"){
cell.setBackground("#a8a18a");
} else {
cell.setBackground("#ffffff");
}

}
"""
"Cell" is an indicator for a cell in an spreadsheet.
"ans": Array of four yes or no values. ie: [yes, no, no, yes]

These two values come from another function that calls to this one from a different file.

Anyone can see where the problem is?
1
M. Volgrand's profile photoAlan Wells's profile photo
3 comments
 
The problem is on this line:

    }else if (b == "1" && a = "1"){

Should be:

    }else if (b == "1" && a == "1"){

You are missing an equal sign:

a = "1"

The way I found this is by deleting large sections of code, and choosing Save, until the error went away, to find the general area of the error.  Then narrow it down from there.
Add a comment...

Burt Lewis

Problem/Coding Help  - 
 
Any ideas on how I could have a shared Google calendar where anyone with a Gmail account can post but only change and delete there own postings. I apologize if this is the wrong place but I'm thinking there might be some scripting. Thanks!
1
Add a comment...
 
Google Calendar issue. It may cause errors for any scripts that use the Calendar service.
http://www.google.com/appsstatus#hl=en&v=status

Update:
Google Calendar service has already been restored for some users, and we expect a resolution for all users in the near future. Please note this time frame is an estimate and may change.
Google Apps Status Dashboard enables users and businesses to monitor the status of individual Google Apps services. Users of Google Apps can now view the status of individual services such as Gmail, Google Calendar, Google Talk, Google Docs, Google Sites and Google Video for businesses. Administrators of Google Apps Premier Edition, Standard Edition, Partner and Education Edition can also view status of the Admin Control Panel.
1
Andrew Roberts's profile photo
 
Yeah, that would happen just I was trying to get a very calendar based app out of the door! All seems fine now.

Bit scary to see a Google service go down, especially if it effected the States too!
Add a comment...

Nitin Bindal

Problem/Coding Help  - 
 
We are trying to merge multiple files into one PDF using GAS. logis are not working....Can anyone help us?
1
Alan Wells's profile photoNitin Bindal's profile photo
2 comments
 
Images, Excel, Pdf, Docs etc
Add a comment...

Josh Walker

Problem/Coding Help  - 
 
Hello Everyone!!
I hope you are all well and having a great day!!!!
What I am looking for is a way to allow users to open https://docs.google.com/spreadsheets/d/1t0tE2MBSFgLZyLPOet2RuTBtg7BX6C1lTPH2mBsqdWI/edit#gid=0 and allow entry to a cell. Once the cell is populated, then lock that cell so it cannot be changed or deleted.

This is an attendance spreadsheet where each of the students teachers would have access. Unfortunately not all of the teachers are supper tech savvy and I am afraid that they might accidentally delete previously entered information.
Drive
Atendance Mock UpSheet1 Probation, Meeting, Letter, CELP ID, Surname, First Name, Level,# of Classes John( Sample Only), Smith John( Sample Only), Smith John( Sample Only), Smith John( Sample Only), Smith, 104/ 105 John( Sample Only), Smith John( Sample Only), Smith John( Sample Only), Smith, 104/ 105 J...
1
Alan Wells's profile photoPipe Dreams's profile photo
5 comments
 
ooo yes, what +Alan Wells​ says, lol, ignore me, sorry 
Add a comment...

M. Volgrand

Problem/Coding Help  - 
 

Hi!
I need to treat an string that includes numbers; I need to extract the number, add 1 to it, and then return it in the same position in that string.

IE:

str: "There are 3 boys"

function addOneBoy(str)
return: "There are 4 boys".

Any help, please?
1
Alan Wells's profile photoM. Volgrand's profile photo
7 comments
 
Thank you!
Add a comment...

Alexandre Raffin

Problem/Coding Help  - 
 
Hi,
I asked a question on SO and was directed here.
The question is about changes in the URL the request are coming from in Apps Script.
The URL was
https://longString-script.googleusercontent.com and it changed to https://sameLongString-0lu-script.googleusercontent.com.
It's the first time it changed since the start of development.
What can cause the URL to change?
Question on SO for reference: http://stackoverflow.com/questions/38079549/google-script-origin-request-url

Thanks.
Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.
1
Add a comment...

Hari Shankar Das

Problem/Coding Help  - 
 
Hello all,
I have been wondering how are the Google Drive Apps created ? I read somewhere that they are created with apps script. But how they are published ? Any reference would really help.

Thanks in advance.
2
Eric Koleda's profile photoHari Shankar Das's profile photo
2 comments
 
Thanks +Eric Koleda for those links. I will go through them.
Add a comment...

Martin Koroudjiev

Problem/Coding Help  - 
 
I am trying to pass some arguments to a script that is deployed as API executable. Unfortunately, for the language I use, there is no Google client library. So I have to construct the POST request myself. Everything is fine except that I can't figure out how to encode the "parameters" param. The request body must be URL encoded so I tried all of these but none works:

function=generateDoc&devMode=true&parameters=aaa
function=generateDoc&devMode=true&parameters="aaa"
function=generateDoc&devMode=true&parameters=["aaa"]
function=generateDoc&devMode=true&parameters[]="aaa"
function=generateDoc&devMode=true&parameters=%22aaa%22
function=generateDoc&devMode=true&parameters=%5B%22aaa%22%5D
function=generateDoc&devMode=true&parameters%5B%5D=%22aaa%22

And as a result my script function doesn't see the passed argument. Reports undefined. Please, help! Thanks.
1
Bruce Mcpherson's profile photoMartin Koroudjiev's profile photo
7 comments
 
That did the trick. Thanks a lot!
Yes, I've authenticated the call alright.
Here is the entire body that works:
{"function":"generateDoc","devMode":true,"parameters":["aaa"]}
Add a comment...

About this community

Google Apps Script is a JavaScript cloud scripting language that lets you extend Google Apps and build web applications. Scripts are developed in Google Apps Script’s browser-based script editor, and they are stored in and run from Google’s servers. Community moderators - Ivan Kutil (Google Developer Expert) - Martin Hawksey (Google Developer Expert) - Romain Vialard (Google Developer Expert) - Bruce Mcpherson (Google Developer Expert) - Riël Notermans (Google Developer Expert) - Eric Koleda (Google) - Spencer Easton

Casey Goforth

Problem/Coding Help  - 
 
Hello,

I am trying to write a google script that, when you click the "button" it is connected to, it auto fills out a specific range of cells with the text "YES". The specific range is one that I will pre-select, meaning no if statement or anything complex needed for selection of range.

The purpose is to make a spreadsheet easier/faster to fill out for someone who's answer is all yes to a specific range of items.

If anyone has any advice on how to accomplish this, it is appreciated.

Thanks,

Casey
1
Casey Goforth's profile photo
2 comments
 
Thanks,

This is the code I ended up using to attach to the "button:
function test() {
//Fill out week 1 Day 1 to all yes
var ss = SpreadsheetApp.getActiveSheet().getRange('C4:C7').setValue("yes");
}

Simon Thomson

Problem/Coding Help  - 
 
Is apps script supported for Google slides. I want to create slides that I can flexibly set a amount of time to show each slide and have different times for individual slides. Can't seem to do this via regular slides app. Thanks for any help, simon
1
Simon Thomson's profile photoMartin Hawksey's profile photo
3 comments
 
+Simon Thomson ... whether the API will do what you are looking for is another question ;)
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Today's post could be useful if you use Slideshare (or want to get data about other's slideshares) it's an Apps Script library that gets slideshare data and stats using the sldieshare API. The example here example just gets all my shared slides and populates a sheet with them.

#gde   #googleappsscript   #blogpost   #slideshare  
1
Bruce Mcpherson's profile photoMartin Hawksey's profile photo
2 comments
 
+Bruce Mcpherson missing some as well. Must dig out and upload my Google Wave talk ;)
Add a comment...

Sandra Vieth

Problem/Coding Help  - 
 
Hi ! :-)
Anyone out there who already has written a script to set alerts depending on valuable dates in a raw (google spreadsheet)? I just tried to set up such a script.... but I am not able to... can anyone help?
1
Alan Wells's profile photo
 
Post a publicly accessible spreadsheet with an example of what cell or column the date is coming from.  We need some specifics.  If it's easy, someone might be willing to help.  If it's hard, maybe not.  We need to know what you know.   Otherwise someone can waste a lot of time telling you what you already know, even if you don't know that much.
Add a comment...

Gavin Trahern

Problem/Coding Help  - 
 
I'm running this script without errors, my spreadsheet has two columns, col A has email addresses, col B has dates. I have some records in there for testing purposes with today's date and my own email address. Although i'm not receiving the emails. Anyone see the issue?

function sendEmails()
{
var app = SpreadsheetApp.getActiveSpreadsheet();
var sheet = app.getSheetByName("Sheet1");
var currentDate = new Date();
currentDate.setHours(0,0,0,0);
var row = 2;
while (!sheet.getRange(row, 1).isBlank())
{
var emailDate = sheet.getRange(row, 2).getValue();
if (emailDate == currentDate)
{
var emailAddress = sheet.getRange(row, 1).getValue();
var subject = "You are scheduled for hall monitoring";
var body = "Hello. This is an automated reminder that you are on the schedule this weekend for hall monitoring. Thank you!";
MailApp.sendEmail(emailAddress, subject, body);
}
row ++;
}
}

1
1
Juber Ahmed 's profile photoAndrew Roberts's profile photo
3 comments
 
The first thing I would check is whether you can do a direct comparison of Date objects (if (emailDate == currentDate). You might have to use Utilities.formatDate() to get them both to a string like "YYYYMMDD hh:mm".
Add a comment...

Jaume Feliu

Problem/Coding Help  - 
 
Anyone know if Is it possible to use Driveapp to share a folder without send notify?
1
Spencer Easton's profile photoJaume Feliu's profile photo
3 comments
 
Thanks +Spencer Easton​​ I've never used Drive service, but I'll try it
Add a comment...

Thomas Mills Hinkle

Problem/Coding Help  - 
 
So I did too much testing today and ran into the error: "Service using too much computer time for one day"

My understanding is this is a limitation per trigger. Does that mean it's per script? Per script per trigger? Per form per trigger? Just trying to figure out if there's a good way to work around it for testing triggers.

I could imagine making a new copy of the script, a new copy of the form, etc., but don't want to waste the time if it won't help.

I don't believe I'm going to actually run into this error in production given our use cases, but I was a little careless with some of the tests I ran and ended up triggering it a bunch more than I needed to.
1
iBO App Service's profile photo
3 comments
 
+Thomas Mills Hinkle 
running and performing or just running?
Add a comment...

Rachel Welsch

Problem/Coding Help  - 
 
Any help would be greatly appreciated. I've had this script that has been working for the past year or so and all of a sudden it stopped working. Now it says invalid email as the error. Any reason why it would all of a sudden stop? Some aspect of MailApp change that I wasn't aware?

MailApp.sendEmail({

to: emailS,
cc: emailM,
noReply: true,
subject: "Sweet Pickle for: " + recR,
htmlBody: '<html><body>' +
'<br/><b>'+recR+',</b><br/>' +
'<br/>A sweet pickle was sent to you from: '+sendER+'<br/>' +
'<br/>'+sweeT+'<br/>' +
'</body></html>'
}); 
1
Rachel Welsch's profile photoAlan Wells's profile photo
7 comments
 
Maybe try using GmailApp instead and see what happens?
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I published something lately that allowed you watch for and react to server side data and positional changes in Sheets from client side add-ons. Well here's an update so it now does the same thing for Docs. It'll call you back if the document content, the selection or cursor changes and you can watch parts or all of the document for data changes.

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

Peter Driessen

Problem/Coding Help  - 
 
Hi All,

I'm uploading files to Google Cloud Storage from google apps script. I tried to add cache-control to the header, but it doesn't work. When I set the metadata on the file in the bucket itself, the metadata is overwritten when I upload the file again. But as [not set] or empty. Who knows the right way to set metadata / cache control to uploaded files?

This is my approach:
var url = 'https://www.googleapis.com/upload/storage/v1/b/BUCKET/o?uploadType=media&name=FILE'
.replace("BUCKET", params.BUCKET_NAME)
.replace("FILE", encodeURIComponent(params.FILE_PATH));

var response = UrlFetchApp.fetch(url, {
method: "POST",
uploadType: 'media',
payload: data,
headers: {
Authorization: 'Bearer ' + service.getAccessToken(),
'Content-Type': 'application/json',
}
});
1
Spencer Easton's profile photo
 
If you are doing a simple media upload the metadata is ignored and must be set by an additional request. multipart and resumable both allow for metadata at upload time.

https://cloud.google.com/storage/docs/json_api/v1/how-tos/simple-upload
Add a comment...