Stream

Join this community to post or comment

Zacc Richards

Problem/Coding Help  - 
 
I have only written code using VB and vb.net and mu knowledge is limited.

I have a macro written in VBA in excel and im trying to migrate to google sheets.
Below is the code im trying to migrate, but due to lack of knowledge of javascript and googlesheets I cannot get this to function.
If somebody could help with the translation I would be truly grateful.

Sub AddRow()

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Rows("4:6").Select
Selection.EntireRow.Hidden = False
Rows("5:5").Select
Selection.Copy
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
ActiveSheet.Paste


ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range(ActiveCell, ActiveCell.Offset(0, 19)).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.349986266670736
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Rows("5:5").Select
Selection.EntireRow.Hidden = True
Range("a1").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True

Call Borders
Call pagebreakborder


End Sub
1
Add a comment...

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

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
shaun piosalan's profile photoRiël Notermans's profile photo
5 comments
 
Yes. You could build a script using the drive API and HtmlService.

Or you could let someone build it...


https://developers.google.com/drive/v3/web/properties
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...
2
Beka Zakaidze's profile photoBen Fletcher's profile photo
5 comments
 
Here's a summary of your options:
1. Find a browser add-on/extension
2. Enable Protection with option to only warn on, not prohibit, changes
or
3. Restructure the entire sheet with a Master and Working version of each sheet. On acceptable edits, copy them from Working sheet to Master. End users always work on the Working sheets. On unacceptable edits copy the edited cells from the Master to Working sheet.

There are no specific event triggers that let the script directly determine how the user accomplished a change -- scripts have no access to intercept or monitor keyboard or mouse activity -- so you'd simply have to examine the before and after picture of the data to determine what was edited and whether acceptable. A simple test might be to reject changes if more than one cell were changed at a time. How you reject the change might be tricky if you don't want immediately overwrite an unacceptable change but still keep the two sheets in sync.

You could keep the Master sheets in an entirely separate spreadsheet or other external source, although that will likely dramatically slow down script execution.

You may also run into quota issues. Also some formatting or conditional formatting may not get copied properly or stay in sync.

I would suggest that you consider the entire approach you have to putting mission critical data in a single spreadsheet that multiple users access and are required to have write access to. Maybe take a look at publishing the sheets in a read-only format and taking input via a form, if you haven't already considered that approach.
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...

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

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

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

venkatesh SQL

Problem/Coding Help  - 
 
Hi, I have a function with parameter and would like to script create trigger for unattended function execution.

function getLastNdays(nDaysAgo) {
  var today = new Date();
  var before = new Date();
  before.setDate(today.getDate() - nDaysAgo);
  return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}

function runNextTrigger() {
 
    var GUID = "!Q@W#E$RI"
    var userProperties = PropertiesService.getUserProperties();
    var nextTrigger = ScriptApp.newTrigger("getLastNdays").timeBased().after(1 * 60 * 1000).create();
    
    var triggerId = nextTrigger.getUniqueId();
    userProperties.setProperty("LastNdays_" + GUID, triggerId);    
}

Even though the trigger is created it does nothing as no parameter value is being passed is there a way to pass parameter to the script create trigger.
1
Peter Roelant's profile photo
 
Try using scriptproperties iso userproperties as when a function is run there's no real user hence no userproperties
Add a comment...

KalyanaKannan P

Problem/Coding Help  - 
 
google apps scripts event for google form submit. i need to return some value to my server when user submit there survey
1
Alan Wells's profile photo김희섭 (Huysep Kim)'s profile photo

Chris K

Problem/Coding Help  - 
 
Any method of recording a macro?
Would be a great feature if not
1
Bruce Mcpherson's profile photo
3 comments
 
Unlike VBA, Apps script doesn't have this feature, and im my view is not is it likely to, since there are many things you can do in the UI that don't have an equivalent in Apps Script , and visa versa.
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...

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
Martin Hawksey's profile photoSébastien Tremblay's profile photo
5 comments
 
Je n'ai seulement que les sessions comme statistique. Est-ce possible d'avoir plus d'info.

Le temps.
L'utilisateur
Les feuilles consulté.

Est-ce que je dois appliquer cette formule sur chaque document partagé?
 ·  Translate
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...

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

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

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

Wajid Nawaz

Problem/Coding Help  - 
 
I have the following script:

function CreateCopy()

{

var source = SpreadsheetApp.openById("1j0LHacJO6_kEKyF1fVlBmvtvSMNhTP2Or-Dx61X3IfU");
var sheet = source.getSheets()[1];

var destination = SpreadsheetApp.getActiveSpreadsheet();

{
sheet.copyTo(destination).setName("02");
sheet.copyTo(destination).setName("03");
sheet.copyTo(destination).setName("04");
sheet.copyTo(destination).setName("05");
}
}


Is there a way for me to obtain the source sheet and copy in the destination one at a time as opposed to receiving all at once. I.E

When i click the CreateCopy function i receive the sheet "02", and then when i click it again i receive sheet "03" etc etc as opposed to receiving all sheets at the same time.

I think what i would like is to provide the user the option to copy this sheet at their will rather than the whole batch arrive.

Your assistance with this will be appreciated

Regards
1
Wajid Nawaz's profile photoJean Gorene's profile photo
5 comments
 
+Wajid Nawaz No problems you are welcome and questions are really justified.
Myself, I also wondered about it, there is not so long.
Nevertheless when a script runs, the best way (for me) is to search for oneself through official documentation or relevant documentation like this : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map
Even here, there are a lot of good informations ... "first choice" by experts ... searching by keyword ;)
Add a comment...