Stream

Join this community to post or comment

Bruce Mcpherson
moderator

News  - 
 
Video for my upcoming Sheets add-on. Chord snip.
as usual, it's open sourced if you want to make your own.

https://youtu.be/T7gt3NVEJOM

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

Wesley Bussche

Problem/Coding Help  - 
 
i am really bad with these scripts apparently. it either doesn't work or tell me im not allowed to continue on certain lines.
could anyone write me a simple script that hides rows if they're blank ? basically i have a checklist that shows people who have not filled in their weekly job. if they have, that row is blank and should be hidden so all the ones that still need to fill their info are under eachother. my sheet is named Memberlist, starts on B109.

thanks in advance to whoever masters these scripts :)
1
Wesley Bussche's profile photoSteve Webster's profile photo
4 comments
 
Have you tried the Remove Blank Rows add-on? 
Add a comment...

shaun piosalan

Problem/Coding Help  - 
 
Searchable Photo Database?

Right now, users upload photos to Drive with a script in Sites. They select one of a several attributes at the time of upload. If the photo has attributes A, L, R, and Z it is placed in folders A, L, R, and Z within the photo uploader directory. If I want to see all the photos with property Z, that's fine. But what if I don't want to see a photo unless it has 7 specific attributes?

How can I do something like, say, a set of checkboxes A-Z and only display photos that meet that criteria?

Any ideas?
1
Add a comment...

Beka Zakaidze

Problem/Coding Help  - 
 
Dear ALL, I have the project created in sheets and using it for past 2 years. I've moded it to perfection and using it is crucial for our company. There are different users with different privileges to work on it, and we have to use filters very often.

And then happens what is my problem: accidental drag & drop...
I want to create some script to disable-prevent accidental drag, drop, cut & Paste. As I searched there R lots of people wanting same. Can someone suggest me what to do? Protection not working because we have to change things...
1
Spencer Easton's profile photoBeka Zakaidze's profile photo
4 comments
 
Yes thats what I've searched already, but I want to know: can we make a script that will popup the confirm window before overwrite something in cell? I think that will be the function we all need...
Add a comment...

Bruce Mcpherson
moderator

Offtopic  - 
 
I don't really like banning people from this forum, but we are seeing a fair amount of rubbish being posted here lately. You usually don't see it as the moderators usually catch it early, but it's a lot of work. This is a G+ community about Google Apps Script and related tech where people come to collaborate and help each other on those topics. Selfies, pictures of cats, get rich quick schemes and stuff about Justin Beiber would be much better posted somewhere else. If you post trash, like this then you'll simply end up being banned, so please try to keep the content relevant. 
35
Mark Schuurman's profile photoValery Orloff's profile photo
2 comments
 
+1!
about "cats pictures" +1000!
Add a comment...
 
I want to hire some help with apps script and google platform.

I have spent many hours/days trying to get the "edit form responses" URL to show up on my google sheet. I want to be able to edit and resubmit a previous form submission from my google sheets page. I think I have read every entry posted in the various forums on this topic but am still getting the apps script error "No item with the given ID could be found, or you do not have permission to access it". I have even started completely over with a simple 1 question form and sheet for responses but can not get the permission issue resolved.

I do not have anymore time for this. I do not have a strong coding background and want to hire a 'consultant' who can fix this and help me with other issues in the future.

I will have many more ideas I would want to implement to improve my business work flow. If you have a strong background in apps script, google sheets, docs, forms, maps, calendar, etc and have the time to work on a few small projects for me on the side, then I would be extremely happy to hear from you!!
1
Stéphane Giron's profile photoshaun piosalan's profile photo
7 comments
 
I've been using a script to do this for hundreds of users and had no issues. I could share it with you if you like. PM me.

Quick thought- This might sound like a silly question, but after finishing your script did you hit the play/triangle button and grant permissions?
Add a comment...

francis fernandes

Problem/Coding Help  - 
 
Working with WSDL web service:

I am looking out for a tutorial on google app that will help me with a webservice
http://api.axonme.com/smsapi.svc?wsdl

I tried with this tutorial here but there are functions depreciated and they say you need to use urlfetchapp.

I am new to google app scripting so would appreciate a step by step guide or something that will help me in working with the SOAP request

2
Add a comment...
 
Google Sheets as a Database – INSERT with Apps Script using POST/GET methods

Google Doc. : https://goo.gl/uP4Kru
Google Photos. : https://goo.gl/photos/Pqh1GgbYB7NFoRZw9


1
Add a comment...

Raffaele Paparella

Problem/Coding Help  - 
 
you are aware of this problem (https://code.google.com/p/google-apps-script-issues/issues/detail?id=5161)?

how do you solve?
Thank you
Google Apps Script issues and feature requests.
1
Add a comment...

Chris K

Problem/Coding Help  - 
 
Anyone know where Google app scripts live?
You have to open a spreadsheet to open the script
So is the script specific to that spreadsheet?
Or is it accessible by all sheets?

MS Office VBA can be written at Application level (all spreadsheets) or Sheet level (just current spreadsheet)
2
1
Faustino Rodriguez's profile photoBruce Mcpherson's profile photo
20 comments
 
+Faustino Rodriguez you'd have to recreate and republish im afraid. When Add-ons first came out i think they could only be container bound - probably because there was no real way to test them otherwise. However, when they introduced the 'test as add-on' feature that problem went away and add-ons can be written as standalone.
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

Amarnadh Mallimoggala

Problem/Coding Help  - 
 
Disabling the columns in google spreadsheets

How can i disable the user from editing the columns in the google spreadsheet . Is there a way to achieve this other than using protection.Protection does not reach my requirements.
1
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
If you are creating add-ons that generate SVG and you need to embed the result in a Google Sheet or Doc, you'll need to convert it to another format first. Trouble is that most svg conversion libraries don't support all svg capabilities. Here's how to do it using your browser API.

#gde   #googleappsscript   #blogpost   #javascript   #svg  
2
Add a comment...

Sébastien Tremblay

Problem/Coding Help  - 
 
Bonjour, j'aimerais avoir un script qui fait le calcul des statistiques d'utilisation d'un google sheet qui est partagé avec d'autres collaborateurs
 ·  Translate
1
Bruce Mcpherson's profile photoMartin Hawksey's profile photo
2 comments
 
Couple of options here https://mashe.hawksey.info/2014/02/tracking-google-sheet-views-with-google-analytics/ A note on the Beacon is it's not 100% reliable - I use it more to track copies of a template
Add a comment...

Richard Anderson

Problem/Coding Help  - 
 
I wonder if anyone here has already scripted a way to mass produce embedded charts. I have a Google Form that is pre-filled with a Title and Document ID in questions 1 and 2 respectively, followed by a few checkbox questions. I want to take the subsequent data in that row in the Form Response sheet, then build a chart or table, and embed it in the document with the corresponding Document ID. Essentially one chart/table per row of data. I am going to try in the meantime, but I know many of you are super savvy with this type of thing. 
1
Bjorn Behrendt (EdListen)'s profile photo
 
I think awesome tables has an add-on that will do that.
Add a comment...

Ravi Kant

Problem/Coding Help  - 
 
Currently i am working on Google APP script. i want to fetch all question, answer with correct option and score. I use FormApp class and i am able to fetch Questions and its answer but i didn't find any option to fetch its Score and correct option.

Note : To set Score and correct option Open Google Form : Click on settings : click on Quizzes tab : on Make this a quiz. When you create Question in bottom section have option answer key.

My question, how to get correct answer option value and points in Google App Form Script.
1
Eric Koleda's profile photo
 
Unfortunately Apps Script doesn't yet support the new correct answer and score features recently launched in the UI.
Add a comment...

Chris K

Problem/Coding Help  - 
 
I used to write MS Office VBA which can be written at Application level which would apply to allllll spreadsheets, such as functions and events and these modules were stored with the excel application it's self, you can also write at workbook level and these modules were stored in the local workbook and only applied to the local workbook

I read that Google script can also be written at global (all books) or local level (current book only) but it doesn't define the difference so I don't know how to recognise which from which

In excel you can see/write/edit both levels from the same window using folder hierarchy just the same as file storage hierarchy window

How do I (a total novice) recognise the difference between a global function and local function in Google app script when I'm looking at it?

Will I recognise it by where it is saved?

Will I recognise it by the font colour?

Will I recognise it by a definition in the script itself?

Maybe global script is written in a different window?

It's there any single defining thing that i can recognise that shouts "this is local script"for this sheet only?

1
Alan Wells's profile photoChris K's profile photo
4 comments
Chris K
 
+Alan Wells thanks
In VBA you can refer to all/any current objects as "Me"
Me.Activecell = 54
Hoping GAS might have similar referencing
Add a comment...

tom rolloff

Problem/Coding Help  - 
 
I am trying to get a script to work on a google sheets, I have a spreadsheet that I have a drop down with times 12:00 PM, 2:00 PM, 4:00 PM, 6:00 PM, 8:00 PM in the dropdown. I am trying to get a script to run that when different times in the drop down are selected it hides cetain columns ie 12:00 PM hides colums 4-8, 2:00 PM hides columns 5-8. any ideas how to get this to work would be awesome Thank you
1
Alan Wells's profile photo
 
Do you have any code?  I think you'll need to install either an On Edit or On Change trigger, and then check the active range cell address.  If the active cell is the cell that the drop down is in, then run the code, otherwise just quit.

function myFunction() {
  var currentCell, curentRange,ss;
 
  ss = SpreadsheetApp.getActiveSpreadsheet();
  curentRange = ss.getActiveRange();
  currentCell = curentRange.getCell(1, 1).getA1Notation();
 
  if (currentCell !== "A1") {return};
 
  //Hide columns
  ss.hideColumn(5);
};
Add a comment...

Dustin Gilbert

Problem/Coding Help  - 
 
I have never done any form of script before, but have an idea with a workbook I have created and am seeing if it is possible to do. I have set templates on one sheet and I would like to create a button for each template which copies and pastes it to another sheet in the same workbook. I am sure this is possible. The tricky part would be to paste each template on the next blank cell in a specified column on the new sheet. Any help on either of these would be amazing.
1
Alan Wells's profile photo
 
I'd start by getting the last row in the sheet

var cellValue,columnData,i,lastRowNumber,sh,ss;

ss = SpreadsheetApp.getActiveSpreadsheet();//Get this spreadsheet
sh = ss.getSheetByName('sheet tab name to get');//Get sheet tab

lastRowNumber = sh.getLastRow();
Logger.log('lastRowNumber: ' + lastRowNumber);

columnData = sh.getRange(2,columnToGet,lastRowNumber - 1);
L = columnData.length;

//Loop through the data until a blank value is found

for (i=0;i<L;i+=1) {
  cellValue = columnData[i][0];
  if (cellValue === "") {
    //Add template

    break;
  };
};

Even if all the columns are not the same length, you need to make sure that you get all the content in the column.
Add a comment...

Half-truth Official

Problem/Coding Help  - 
 
I've been consulting this very helpful thread about adding a note that indicates when a given Sheets field was last updated:

https://productforums.google.com/forum/#!topic/docs/_oFmvMZUCqg

I'd like to modify it so that it includes also includes the name of who changed it. I know hardly anything about code but it appears that lastModifyingUser.displayName would be the appropriate place so start, so I tried this:

function onEdit(e){
// Set a comment on the edited cell to indicate when it was changed.
var range = e.range;
range.setNote('Last modified: ' + new Date());
var range = e.range;
range.setNote('By ' + new lastModifyingUser.displayName())
}

and it doesn't seem to work. Does anybody have any suggestions?

thanks!
1
Add a comment...
 
Hello Everyone!

I would like to ask a few questions regarding how to receive email notifications across my team if certain triggers are met. Please open this Tracker to look at it and see if you can help. Thank you so much in advance!

Columns in question:
Column A - Employee

Column B - Date

Column J - Con

Column K - App

Column L - Travel

1) I would like to have a script written that associates emails with each employee separately, "A" "B" "C" etc.
a) Employee A has email A@email.c0m
b) Employee B has email B@email.c0m
etc.

2) If in Column B - Date, the date is 1 day before "Today's" date, I would like an email sent to said employee, "A"/"B"/"C" etc. if one or all of the following triggers are met:
a) Column J - Con - Conditional Formatting has an "N" in the cell.
b) Column K - App - Conditional Formatting has an "N" in the cell.
c) Column L - Travel - Conditional Formatting has "In Progress" in the cell.

3) The reason for the email notification is to notify each employee on the team that something is missing. It will remind the employee to follow up and make sure to gather some information externally.

4) We currently have a sort function and an archive function in place. If by creating this new script(s), will it affect the other 2 functions in place?

Please let me know if someone can help out with a script that can make these functions possible. If you need more information, I am happy to provide. Thanks again!

-Art
1
Add a comment...