Tag Info

Hot answers tagged

25

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.


12

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


10

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

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


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


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

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

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

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

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


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

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


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

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


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

There are some inaccuracies in your code. I will try to guide you through the code. Your code alone will not work, but you probably know that first declare the active sheet (or spreadsheet) var sh = SpreadsheetApp.getActiveSheet(); determine the last row in the sheet var row = sh.getLastRow(); get the range and get the values var aData = sh.getRange(1, 2, ...


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



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