Stream

Join this community to post or comment

Luca Fancello

Problem/Coding Help  - 
 
Hi everyone!

We are currently trying to build a script for google spreadsheet. This spreadsheet is a dashboard and we would like to underline every day the entire row that represents the data of the day.

There are several spreadsheets in the documents thats needs to be underline.

We have:

function testcolor() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
if (sheets.length > 1) {
Logger.log(sheets[1].getName());
}


var range = sheets.getRange("A2:AB2");
range.setBackground("yellow");
}


But unfortunately it isn't working for now. do you see any potential errors?

Thank you,
Luca
1
Spencer Easton's profile photoBenoit Flama's profile photo
4 comments
 
"var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();" returns an array of sheets. So the "var range = sheets.getRange("A2:AB2");" cannot work, you need to specify which sheet of the array, for example "var range = sheets[0].getRange("A2:AB2");"
You can also loop through the sheets :
for (var s in sheets){
sheets[s].getRange... do something
}
Add a comment...

Christian Torres M

Problem/Coding Help  - 
 
Hi guys,

I have a doubt about one stage. This is:
- How does work GAS when an addon is running in a shared spreadsheet. specifically when my addon catch user active email. I did tests and sometimes get my email and sometimes catch email of another user (This another user has permission to edit). Is this an issue of GAS or do I have errors in my code?
1
Christian Torres M's profile photoSpencer Easton's profile photo
3 comments
 
Check out the Apps Script Add-on Community. That's where lots of the top Add-On developers hang out:
https://plus.google.com/u/0/communities/117193953428311185494

For your question. The add-on runs "as the user" even in a shared spreadsheet. What does Session.getActiveUser().getEmail() return?
Add a comment...

Eric L.

Problem/Coding Help  - 
 

Does the range class seem buggy for anyone else? I have a line that hides a column, and it fails with the below execution log, yet when i create a simple function that has the same parameters, it works. But, when I hardcode the value 1 into the code I'm using, it still fails with the same error.

Sheet.hideColumns([1]) [0.101 seconds]
[16-05-03 19:25:57:879 PDT] Execution failed: The name given to this range is invalid. (line 2464, file "Code") [3.94 seconds total runtime]

line 2464: sheet.hideColumns(recIdColIndex);
1
Jean-Pierre Verhulst's profile photo
 
+Eric L.
hideColumns takes an integer as it's parameter (the columnIndex) whereas hideColumn takes a range as it's parameter.
Both are from the class Sheet.
Add a comment...

Christine Ryder

Problem/Coding Help  - 
 
I keep getting the following message in an email, but the link provided gives an error message.

Your script, Form Publisher, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here. https://script.google.com/a/macros/lfanet.org/triggers?id=MG6jxwofUQptdY_KUaF_P0VDOcoke7-2l

I didn't write the script. I am using it as an add-on for a form I created. What do I do?
1
Steve Webster's profile photoAlan Wells's profile photo
2 comments
 
You Form has an "On Form Submit" trigger.  You probably installed a trigger from the code editor, under the "Resources" menu at some point, so that the Add-on will run, OR the code created a new trigger from the code.  In any case, the first thing you should probably do, is review all your installed triggers, and see if you recognize which trigger the error is coming from.  Did you copy and paste the code into your script editor?  In other words, I'm assuming that you have access to the code.
Add a comment...

happy jill

Problem/Coding Help  - 
 
Totally in over my head here. I have a simple spread sheet with columns Title, Action item, date, owner and in some cases final date. I want to use that to populate a google calendar. I've literally spent 12 hours today scouring the forums, but this is not my cup of tea. Any suggestions? thanks!
1
Alan Wells's profile photoMichael Pietschmann's profile photo
8 comments
Add a comment...

Jeff Bauersfeld

Problem/Coding Help  - 
 
Hello. I'm writing a simple script that updates my main membership sheet on open with data from individual sheets tied to JotForms. Everything about the code is working save for the moveTo() call. It produces a vague "Service Error: Spreadsheet." My code is here:

function onOpen() {
  var allss = SpreadsheetApp.getActiveSpreadsheet();
  var allsheet = allss.setActiveSheet(allss.getSheets()[0]);
  var alllastrow=allsheet.getLastRow()+1;
  var allselection = allsheet.setActiveSelection("A"+alllastrow);
  allselection.setFormula('SPLIT(C'+alllastrow+', " ")');

  // Individuals  
  var indss = SpreadsheetApp.openById("1WocI9vNQ9uxuuryWUrZgQgjPWBkZfCykID7IJfWL4JY");
  var indsheet = indss.getSheets()[0];
  var indlastrow = indss.getLastRow();
  var indselection = indss.getRange("C2:F" + indlastrow);
 
  allselection = allsheet.getRange("C" + alllastrow);
  indsheet.getRange(indselection).moveTo(allsheet.getRange(allselection));
}

Yet even attempting to troubleshoot the issue by making the range of each a single hardcoded cell still produces the same error.

  indsheet.getRange("C2").moveTo(allsheet.getRange("C69"));
1
Spencer Easton's profile photoJeff Bauersfeld's profile photo
2 comments
 
+Spencer Easton Thank you so much. I wish I asked this an hour prior :p
Add a comment...

Danny B

Problem/Coding Help  - 
 
Hi! Hope everyones having a great wk end. I have been trauling the net for a solution to the following seemly simple script issue:

Currently my script (below) sends an email when the word NONE appears in the first (active sheet). The email it sends includes the name of the sheet too. However - i need it to be able to find the word NONE across all sheets in the document (not just the active sheet), and each time, include which sheet and cell it was found in. Any ideas would be greatly appreicated! Cheers!

function sheetTracker(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var cell = ss.getActiveCell().getA1Notation();
var sheet = ss.getSheets()[0];
var sheetName = sheet.getSheetName()
var checker = "NONE"
if(ss.getActiveCell().getValue() === checker){
MailApp.sendEmail('someemail@blah.com', 'KPI has been met. WOO HOO', 'KPI met in '+cell+' in '+sheetName+'. ROCK ON');
}
}
1
Alexander Ivanov's profile photoDanny B's profile photo
5 comments
Danny B
+
1
2
1
 
Thanks mate! all good!
Add a comment...

Evan Sullivan

Problem/Coding Help  - 
 
Looking to pull information off of a website (IRS mileage rate).  I am using UrlApp.Fetch to get the HTML from a site that has it, wondering how I could extract just certain text out of that HTML?

Thanks!
1
Alan Wells's profile photoEvan Sullivan's profile photo
29 comments
 
Thank you so much.  Going to look at the code, and try and learn what you did.  Have a great weekend!
Add a comment...

Chuck Grigsby

Problem/Coding Help  - 
 
X-Frame-Options issue: I have a sidebar and a script on a spreadsheet. I want this to be accessible to users who are not logged in. Is this possible? i keep getting this
"in a frame because it set 'X-Frame-Options' to 'DENY'" error
Any suggestions for this? Thanks
1
Chuck Grigsby's profile photo
2 comments
 
Been digging around and not having much luck. Tried the trigger way but it doesn't execute like it says it does for anonymous users. I saw a bug for it online already so another workaround I was thinking of google form? i tried onFormSubmit() with just a log message but i submitted a response and saw nothing in the log so not sure how to go about it. I just want the user to be able to enter info, and when submitted it runs my script that updates the existing row if it finds one
Add a comment...

Mark Ramsay

Problem/Coding Help  - 
 
Calls to Classroom.UserProfiles seem to slow attached script down hugely. Anyone got any experience/suggestions?
Supplies, equipment and publications for schools, school administrators, teachers, educators, students and parents.
1

Evan Sullivan

Problem/Coding Help  - 
 
I am using this URL to export a sheet to PDF.  Works great, except it does not export an image that's in the sheet.  Anyone have any details on the URL parameters so I can figure out how to add it?

  var url = "https://docs.google.com/spreadsheets/export?id="+ssID+                                                         "&gid="+gid+
                                                        "&exportFormat=pdf&"+
                                                        "size=0&"+
                                                        "fzr=true&"+
                                                        "portrait=true&"+
                                                        "fitw=true&"+
                                                        "gridlines=false&"+
                                                        "printtitle=false&"+
                                                        "sheetnames=false&"+
                                                        "pagenum=false&"+
                                                        "attachment=true";
1
Evan Sullivan's profile photo
12 comments
 
I adjusted my script to insert it a different way.  good now, Thanks +Spencer Easton 
Add a comment...

Ben Edmonds

Problem/Coding Help  - 
 
Wonder if anyone has an idea on who one would validate against future dates on a Google Form?

In my context, users are submitting details of their email inbox twice per day. This includes: their name, the total number of messages, and the date of the "stalest" message. The title of the field is "Date of oldest message in inbox". We then use this data to understand how the whole company is performing with email.

How would one validate for this with GAS?
1
Spencer Easton's profile photoBen Edmonds's profile photo
4 comments
 
Thanks Spencer.

Do you know if it definitive that pre-submission validation of this nature CANNOT be done in Google Forms yet?
Add a comment...

George Jenkins

Problem/Coding Help  - 
 
I realise this is an Excel VBA question, but I think the Google Apps Script community would be best placed to answer this as it has probably been covered before.

I am self-taught in VBA and have started feeling my way with Javascript and HTML which I have not used before. I am looking to create a macro that will run when a workbook gets closed. The data in the active worksheet will transfer (and overwrite) to an existing Google spreadsheet. So I end up with the cells that are in the Excel workbook duplicated in the Google spreadsheet.

I have looked at a post on Stackoverflow which refers to creating a Form and then using XML, but I couldn't get that to work. From trawling the net I found a few things mentioned by Bruce McPherson that looked promising. http://ramblings.mcpher.com/Home/excelquirks/dbapps/vbasheetsrealtime

I am hoping to use this data to auto-email various individuals when they are to create reports. I also want to set a notification on the spreadsheet so that it checks the dates without a user having to open the spreadsheet.

Is what I described above something that seems plausible? And with regards transferring data from Excel to Google Spreadsheet (via VBA) is there something else I should be looking at?
1
Bruce Mcpherson's profile photoGeorge Jenkins's profile photo
2 comments
 
Thanks for that Bruce. I tried to open your cDataSet workbook last night, but I only have XL2003 so crashed spectacularly. I will try connecting to my work computer tonight and see how it goes.

Really appreciate your feedback and contributions. It gives some of us mere mortals hope. :o)
Add a comment...

Peter O'Riordan

Problem/Coding Help  - 
 
This is a partly technical, partly workflow question.

I am trying to set up a timekeeping workflow for our small nonprofit. I would like to do this using forms (similar to the Paid Time Off workflow that Google offers and which I've implemented): https://docs.google.com/document/d/1d3IZxwjZqnUxijtFlA9Dk9t0WbZvva_NwEO8QIGAb88/edit#

However, beacause Google Forms don't seem to allow complex data entry, I created the following spreadsheet: https://docs.google.com/a/breakthroughsv.org/spreadsheets/d/14WKpbvxnqwPzjF3QWW5cazqG7X24wU7qXjtQ7kqDS3Y/pubhtml?gid=1426605290&single=true

This spreadsheet does what I want in terms of capturing hours worked but I am struggling to come up with a workflow that would
- remind employees to enter their hours
- automatically email their manager when they submit their timecard
- notify the employee that their timecard has been approved or denied.

The workflow that Google offers for Paid Time off does all these things exactly as I want them but that workflow is driven by the employee submitting the form. Ideally I'd like to embed or link the contents of the spreadsheet in the form so I can reuse the Paid Time Off script that does notifications, approvals, etc. I'd appreciate any suggestions on how to proceed. I am not wedded to using the spreadsheet I put together if there are better ways to go.

Entry Type, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday. 6. Hours Worked, Start, Start, Start, Start, Start, Start, Start. 7. Stop, Stop, Stop, Stop, Stop, Stop, Stop. 8. Hours, Hours, Hours, Hours, Hours, Hours, Hours, 0:00 ...
2
Alan Wells's profile photoPeter O'Riordan's profile photo
9 comments
 
Given that a spreadsheet is the way to go for data entry, is there a way to trigger a script to run when someone enters their time? Scripts typically have an "On Form Submit" trigger but I don't know of any way to generate an equivalent trigger when someone updates a spreadsheet. There's no "save" function in Sheets, so there needs to be some explicit signal from the user that they have finished with an entry.

I can get most of what I want for the workflow if I can trigger a script when the spreadsheet is updated.
Add a comment...

Shirley Wong

Problem/Coding Help  - 
 
We have been using Subscribe to Change for Viewers since Oct 2015 and there is no issue. Recently, found out that there is no notification has been sending out for one user that has created a new post on her page and this user has been posting a new post and no issue previously. The issue only occurs on 29 Mar 2016 till today. Another user that create a new post, notification will send out.

Anyone can help on this matter. Looking forward hearing from expert.
1
Spencer Easton's profile photo
 
I'm guessing this is for Google Sites. Are these new posts being made with Google Apps Script? If not the best place to get an answer is from GAFW support: https://apps.google.com/support/
Add a comment...

Hayato Kamono

Problem/Coding Help  - 
 
Hi! I'm encountering 404 error when I try to post request to google execution api from my chrome extension.

Does anyone know how to solve this problem?
1
Alan Wells's profile photoHayato Kamono's profile photo
3 comments
 
yes, I linked to stack overflow question page.
Add a comment...

Christian Torres M

Problem/Coding Help  - 
 
Good day everyone,
I have a doubt about how create a Login with HtmlService in Spreadsheet. I'd like show sidebars, custon functions, functionalities according to user logged. Someone has an example or something look like this? Thank you all.
1
Steve Webster's profile photoChristian Torres M's profile photo
4 comments
 
Very useful advices. Thanks +Steve Webster  
Add a comment...

Lenny Cunningham

Problem/Coding Help  - 
 
How do you set the Admin APi to read only so users using an add on don't get "Not Authorized for this resource/api"?
1
Lenny Cunningham's profile photoSpencer Easton's profile photo
3 comments
 
+Lenny Cunningham You should be able to create a group and give the group read access.
Add a comment...

Gabriel P

Problem/Coding Help  - 
 
Automatically re-number values when inserting a new row in Google Sheets

(re-posting this from the Google Docs Help Forum)
You know how in Word, when you have a list of numbered values, and they automatically update their numbering when you insert a new row in-between old values? Is it possible to do that, but for google sheets?

So for the google sheets example here: http://i.imgur.com/nMGMlPV.png
...would it be possible to insert a row in-between rows 7 and 8, then have all rows below the newly inserted row be increased by 1 to compensate for the change? ie: values that say sector 4 will now say 5.

Alternatively, would it be possible to also keep the values the same when inserting a new row, if that new row is between sectors of the same name? For example, if a new row is added between rows 6 and 7, the sectors would still read "3" since the new row is being added in-between sectors of the same value.

I know that's a lot to work with, so I wouldn't be surprised if this isn't possible, but I wanted to check here just to make sure!
1
Gabriel P's profile photoAlexander Ivanov's profile photo
3 comments
 
+Gabriel P , I could offer you something sensible. You need to describe your issue.
Add a comment...