Stream

Join this community to post or 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
Peter O'Riordan's profile photo
5 comments
 
OK, I saved it to my personal account and uploaded it from there. New link is https://docs.google.com/spreadsheets/d/1subFAEtGTLvw8V7jE4Cz0vR-OXvU_3NZQFB7KS3zpQg/edit?usp=sharing
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 photo
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...
 
Reminder:

Last October the EMULATED and NATIVE modes for Apps Script's HtmlService were deprecated, and a gradual sunset plan was announced.

As of today, all scripts, including existing ones, will default to IFRAME sandbox mode unless NATIVE mode is explicitly specified.

If you have scripts, web apps, or add-ons that you did not migrate over to IFRAME, they may function differently than expected (or not at all).

For now you can still use NATIVE mode if you explicitly set it. However, NATIVE mode will be sunset permanently on June 30, 2016, so it is recommended that you migrate now if you have not done so.

Happy scripting!
6
6
Wesley Chun's profile photoRory O Connor (rocits)'s profile photoGiuliano Ribeiro (ilegra)'s profile photoAlexander Ivanov's profile photo
 
hmm, that might be the reason why my add-on stopped working.
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
Danny B's profile photoAlexander Ivanov's profile photo
4 comments
 
+Danny B , I've copied the spreadsheet but it works. Could I help you more?
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...
 
Signature Capture in GSHEET
A new company wants to have a "sign-off" sheet connected to a drive document/gsheet.

Does anyone have an idea how this can easily be implemented such that a tablet could be used to discuss work to be done, then get a customer signature?

Thanx!

1
1
Steve Webster's profile photoJim Garner's profile photoAlex Vitte's profile photo
2 comments
 
Steve, I have sent you a Hangouts Invite.
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...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
 
Here's a simple library to access Firebase JSON API from Apps Script.(server side) . It can also return Promises rather than results if you prefer. I'm using it to manage player data for the big quiz bigQuery app. I had originally planned to use Google Play Game Services for that, but it was just way too laborious to get going (as an aside I think Google have got this game play api very wrong). Using Firebase instead is a breeze.

#gde  #googleappsscript #blogpost
7
Bruce Mcpherson's profile photoFaustino Rodriguez's profile photo
5 comments
 
agreed +Bruce Mcpherson , we better forget about a DbAbs driver for FB
Add a comment...

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...

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

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...

Alan Wells

Offtopic  - 
 
I found a couple of Chrome extensions that I'm using togther to change the color and font size in the Apps Script editor:

Alpha Text:

https://chrome.google.com/webstore/detail/alphatext-make-text-reada/lpcaoilgpobajbkiamaojipjddpkkida?utm_source=chrome-app-launcher-info-dialog

And Care your Eyes:

https://chrome.google.com/webstore/detail/care-your-eyes/fidmpnedniahpnkeomejhnepmbdamlhl?utm_source=chrome-app-launcher-info-dialog

The only issue with Care your Eyes is that the text is a little fuzzy, but I increased the font size with Alpha Text.
Customize and change the appearance of online articles to enhance text readability
6
Alan Wells's profile photo
 
Care your Eyes, I'm using Blacklist Mode, and Night Mode.  Brightness 85, Contrast 105
Nightmode - Always
Custom color doesn't seem to work.

Alpha Text
Font Size - Doesn't seem to have any effect
Veranda
Line Height 1.2
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...

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...

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...
 
Filter View Archive
We have a lot of collaboration on MANY sheets within one Google Sheet. This necessitates some good Filter Views, and we have them! ...Too many!

Most reading this will know that with many collaborators, FILTER VIEWS must be used, versus just FILTERS, in order to keep everyone from having to look at the same filter setting. And that is why we need to manage them.

It is getting impossible to manage so HOW MUCH woudl it cost to have one of you fine folks build this for me?
I am also wondering if there is an add-on or if it would be too difficult for me to write something that would allow us to ARCHIVE Filter Views, and then to RECOVER them when needed, in order to keep our house-cleaning more simple.

We have much that should be implemented in such an add-on. Another thought in the management of Filter Views is that RANGE is almost never on less than the entire sheet, so it would be nice for us to have an "auto range update".

Any thoughts on this would be appreciated. I did spend a lot of time searching on possible solutions and found ZERO. I know I can make a copy of our whole Sheet to preserve a set of filter views, but getting them back is impossible. And since many of our sheets are linked together in different ways, it is unwieldy to attempt to have all collaborators MOVE as we do that.

I am not sure I could do the scripting/programming necessary for this option, but if that is the only way, I may try, or at least, seek to pay someone to do it. It looks like a needed capability.

Thanx!

1
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...

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...

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

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you have an htmlservice app being accessed 'as the user', you may want to to personalize that with the People API - here's an example.

http://ramblings.mcpher.com/Home/excelquirks/bigapps/bigquiz/peopleapi

#gde #googleappsscript #blogpost
8
1
Bruce Mcpherson's profile photo
Add a comment...