Tag Info

Hot answers tagged

27

Google added this feature now in the "new spreadsheet format". This can be enabled for new documents like follows: On "MyDrive" go to settings choose Editing and then "Try the new Google Sheets". Please be aware that as of now not all features are complete, so treat it as a beta version. One of the changes in this new spreadsheet is the possibility to ...


14

Leverage Google Apps Scripting to extend the UI Update: This script is available in the Script Gallery. Just search for 'Text to Columns', install the script, and refresh the spreadsheet. Note: it may be slow to load the first time. Text to Columns, is a very handy feature and one of the reasons that a lot of Google Spreadsheet users go back to using ...


14

Yep its called Google Apps Script. They just had a Apps Script Hackathon! They have some good examples and tutorials on the resource page. Someone also coded "Game Of Life" in a script :) They also had a talk at Google I/O about it.


14

There's currently no way for a formula to do this and no default Google feature. However, there are a few third party scripts that will do this for you: Installing the script Open your spreadsheet Click on Tools menu Click on Script Gallery Search for Zebra Stripe Install Read the warning (and if you agree go on) Authorize Executing the script Click ...


13

Yes: =ArrayFormula(SUMIF(ROW(A1:A10);"<="&ROW(A1:A10);A1:A10)) The numbers in the green circle are the ones being summed, the ArrayFormula is populating all the others based on the cell it was pasted into, in this example I pasted in A11, B1, and C1 to show its effect. This spreadsheet that I made a while ago attempts to explain how MMULT can be ...


9

The follow formula will do just that; text-to-column: A1=5,233,6,2,6,7,2,2,6,6 A2=SPLIT(A1;",") And the next; text-to-row: A1=5,233,6,2,6,7,2,2,6,6 A2=TRANSPOSE(SPLIT(A1;",")) UPDATE 03-02-2013 If you split the result of A1 and paste the values, it will give the same result as all the lines of code used in the OP's answer. I gave it a shot at it as ...


6

The following code will add a menu to the Spreadsheet: Detective > Trace Dependents Selecting this will add a note to the active cell with all dependent cell references. You could add a similar function to the traceDependents function to search for the text within the active cell for a Search in Formulas function. I'll leave that as an exercise for you. ...


6

To @Jacob's claim of impossibility, I refute it thus... (but thanks for the improved speed) using: =sumIfBgColor("#ffffff", A1:A10, COLUMN(A1), ROW(A1)) with the following functions will do what you want. /** * Sums cell values in a range if they have the given background color * * @param {String} color Hex string of color eg ("#ffffff") * ...


5

When passing a Range to a Google spreadsheet function, the framework executes paramRange.getValues() implicitly and your function receives the values in the Range as a 2-dimensional array of strings, numbers or objects (like Date). The Range object is not passed to your custom spreadsheet function. The TYPEOF() function below will tell you what kind of data ...


5

You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar: Google Spreadsheet Timestamp? function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet var r = s.getActiveCell(); if( ...


5

The following code retrieves the users, present within the Google Apps Domain: function doGet() { var app = UiApp.createApplication(); var users = UserManager.getAllUsers(); var flexTable = app.createFlexTable().setBorderWidth(1); for (var i=0, len=users.length; i<len; i++) { var user = users[i]; flexTable.setWidget(parseInt(i), 0, ...


5

You will need to write a Google Apps Script for that. You could let the first row of the spreadsheet be field names, and create a template document where the fields are referenced like [FIELD]. So if your spreadsheet looks like: NAME | STREET | ZIP | TOWN --------------------------------------------- Vidar | Karl Johans gate 15 | 0200 | ...


5

I've created a small Google Apps Script (GAS) snippet, to do the work for you. Code function sheetRange(targetName,int) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var asName = ss.getActiveSheet().getSheetName(); var tgSheet = ss.getSheetByName(targetName); var output; if(targetName == asName) { output = "Error: target sheet is active ...


4

So I've searched long and hard for a good answer to this and here is what I have found: an unmodified range parameter passes in the values of cells in the range, not the range itself (as Gergely explained) ex: when you do this =myFunction(a1:a2) to use a range in your function you need to first pass the range in as a string (ex: =myFunction("a1:a2") ), ...


4

range is treated as javascript's 2d array. You can get the number of rows with range.length and the number of columns with range[0].length. If you want to get the value from row r and column c use: range[r][c].


4

I have posted a script to the Script Gallery called "Zebra Stripe Menu". If you add that to your spreadsheet, and make sure to open and close the script editor (there is a bug with recognizing the script). You will have a menu called "Zebra Stripes" and there you will find a couple of options for striping sheets and groups of cells. Some of the other options ...


4

Select row 1 Color it with Fill color Select row 2 Color it with another color with fill color Select row 1 and 2 row and copy it Select the full sheet Go to Edit → Paste special → Paste format only Job done.


4

Google's own post explains how to set up the feed data in one sheet and the template in another, rather than a Google Spreadsheet + Google Doc: https://developers.google.com/apps-script/articles/mail_merge However, the end result is for the MailApp to send an email, rather than the desired "cloned" document. I would suggest combining the tutorial and ...


4

This little script will a retrieve named range and make a summation: function namedRange() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getActiveSheet(); var nRange = ss.getRangeByName("budgetItems"); var data = nRange.getValues(); var sum=0; for(var i=0; i<5; i++) { sum += parseInt(data[i]); } ...


4

Starting with the following line of code: var range = sheet.getRange("A1:B10"); The sort can be performed in different ways: range.sort([1,2]); range.sort([{column: 1, ascending: true}, {column: 2, ascending: true}]); Both ways are identical as the sort type is by default ascending, but the second option allows for different sort order when TRUE is ...


4

With a normal formula it is not possible. A custom function doesn't works as well (pure JavaScript). Therefore I wrote this little script to act as a work-around. Code // global var ss = SpreadsheetApp.getActiveSpreadsheet(); function onOpen() { var menu = [{name: "Complete Range", functionName: "sumColumn"}]; ss.addMenu("Sum", menu); } function ...


4

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly. What you tried to do is most probably the following: In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse. Update If you really want to get the result in A1, then you need to use a script. Code // global var ...


4

Using your example, this formula can be used in row 2: =TRANSPOSE(FILTER(B$2:B;MMULT(C$2:E=B2;TRANSPOSE(SIGN(COLUMN(C$2:E$2)))))) but will need to be filled down as far as is required. Hopefully you can adapt the references to your actual data, but comment back if this is an issue. MMULT Usage If you require a single formula that will automatically ...


4

I think you can do all that, with the code below. Code function onEdit(e) { var sName = e.source.getActiveSheet().getSheetName(); if(sName !== "Logging") { var value; var mA1 = e.range.getA1Notation().split(":")[0]; var time = new Date().toTimeString(); if(typeof(e.value) == 'string') { var aCell = e.source.getRange(mA1); ...


4

Google offers a limited amount of computing power to Sheets users. This restriction is particularly noticeable for scripts: they take a long time to run, and trying to invoke a script too often results in errors of the type you encounter. The task you are trying to do here could be done with a single script invocation. It may still take a while to process ...


4

Add a new select with a new id, and assign a set of values the same way. This should give a good idea how to fill multiple drop-downs from one data array where each column sets the drop-down value. function onSuccess(values) { //values is a 2d array. var opt, dropDown; for(i = 0;i < values.length; i++){ dropDown_one = ...


3

Add the following line of code: SpreadsheetApp.flush() See reference: flush


3

=index(A:A;max(arrayformula(row(B:B)*(B:B="X"))))


3

Well, there's always installing Wingdings, but doesn't gmail have its own set of emoticons? This would be rendered as a tiny image, rather than a font glyph, and therefore independent of any fonts you may not have installed.


3

It is possible using the Google Apps Provisioning API. This will let you update the changePasswordAtNextLogin property to true. Here will the flow. Make a query for user account details. Check updated field. If it is older than 3 months, update the changePasswordAtNextLogin property to "true". (Here I am assuming that no other field has been changed in ...



Only top voted, non community-wiki answers of a minimum length are eligible