Hot answers tagged

35

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


17

Leverage Google Apps Scripting to extend the UI Text to Columns, is a very handy feature and one of the reasons that a lot of Google Spreadsheet users go back to using Excel. Until, Google decides to officially support the feature, this solution can be used as a polyfill to add in the functionality. Here's the code: function onOpen() { var ss = ...


17

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


16

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


12

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


9

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


8

Use the code below to copy also formula's as normal values. Add the code by selecting Tools from the spreadsheet menu. Then select script editor and add the code. Make sure to press the "bug" button and authenticate the script. Code // global var ss = SpreadsheetApp.getActive(); function onOpen() { var menu = [{name:"Add New Last Row", functionName:"...


7

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


7

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") * @...


6

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


6

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


6

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


6

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


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

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


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

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


5

According to Google: older_than, newer_than — Similar to older and newer, but allows relative dates using d, m, and y for day, month, and year Example: newer_than:2d Meaning: Finds messages sent within the last two days. With this you can create a filter deleting these, and even only the mails in a specific label. Source.


5

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.


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


5

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


5

The "library solution" is actually your best bet. It is not as complicated as it sounds, but it takes a little bit of one-time work. First you create your script in one spreadsheet (let's call it the Master spreadsheet). Save a version of the script by clicking File → Manage versions in the script editor, and give your version a name: Close this ...


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

Provision API has gone the way of the Dodo and now you have to rewrite your script and add some extra security approvals along the way. I got the info finally after a whole bunch of searching, after my script failed to day from here: http://thegapps.com/7/how-can-i-force-my-users-to-reset-the-password?show=8#a8 below is a summary (without the pretty ...


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


4

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


4

With the following custom function (as you're accustomed to writing in JavaScript) it is possible as well: Code function myReverse(range) { var output = []; if(range[0].length > 1) { throw ("function can only handle a column range"); } else { for(var i in range) { var name = range[i][0].split(","); output.push([name.reverse()....


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



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