Tag Info

Hot answers tagged

19

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

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


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.


11

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


8

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


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

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


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

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


4

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


4

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


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

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


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

You can add your script as a library in the new spreadsheet. Lets start from the beginning. This sample code will add a string to the active cell: function libTest(text) { var sh = SpreadsheetApp.getActiveSpreadsheet() .getActiveSheet().getActiveCell(); sh.setValue(text); } Save the script, by creating a version: Next, copy the Project Key from ...


3

The key approach is to minimize the API calls. Performing those in for loops will slow down the process considerably, especially with 900 rows. I've re-worked your code in such a way, that it calls for the Spreadsheet API only twice: function open_interventions() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // start logger time ...


3

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]); } ...


3

This is Eduardo from the ShuttleCloud & Gmail Meter team. We've recently updated Gmail Meter. If you have previously tried Gmail Meter but had problems with it, please try removing it and installing it again. Uninstalling Gmail Meter is not easy due to the underlying Google Apps Script code it's based on. Here are instructions to uninstall Gmail Meter: ...


3

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


3

Code I've reduced your code to the following: function onEdit(e) { var sName = e.source.getActiveSheet().getName(); if(sName != 'Image Timeline and Status') { var s = e.source.getActiveSheet(); var c = s.getActiveRange().getColumnIndex(); var r = s.getActiveRange().getRowIndex(); if(c == 8 || c == 9 || c == 10) { var username = ...


3

Gmail.search would only return the first few hundred emails and if you would like to retrieve the later message, you may have to specify the offset parameter. I previously wrote a Google Script for extracting email addresses from a Gmail folder and it does work with the previous messages as well.


3

Don't use a custom formula--instead process all the values into an array then have the array written to the sheet. This will not adjust as your spreadsheet changes, but you should be able to modify this to look for the last value using a combination of the match and len functions in the sheet to id the starting and ending rows. function straightToText() { ...


3

For Google Spreadsheets, it is possible by writing a script: function copyValuesAndFormatting() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var fromRange = sheet.getRange("A2:A"); var toRange = sheet.getRange("B2:B"); var values = fromRange.getValues(); var fontColors = fromRange.getFontColors(); var backgrounds = ...


3

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


3

The following piece of code will create a Google Document and will insert a dynamic text, as a paragraph, into the newly created document. All is done from within a Google Spreadsheet. Code // global var ss = SpreadsheetApp.getActive(); function onOpen() { var menu = [{name: "Create Document", functionName: "createDoc"}]; ss.addMenu("Extra", menu); } ...



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