59

When you switch to a different sheet in Google Spreadsheets, pay attention to the URL in your browser's address bar. At the end of the URL you should see something like: #gid=0 This number changes when you switch sheets, and specifies which sheet to display. Copy the entire URL and create a hyperlink to it with this formula: =hyperlink("https://docs....


44

Yes, you can create a custom function for that. Click Tools → Script Editor. Select Create blank project. In the script editor window, paste the following code: function encode(value) { return encodeURIComponent(value); } Save the script (give it a name if prompted), and return to your spreadsheet. Now, you may use the function as =encode. If ...


40

Google Sheets supports the following keyboard shortcuts: Ctrl + Shift + : is the keystroke to insert time. Ctrl + ; is the keystroke to insert date. Ctrl + Alt + Shift + : is the keystroke to insert date and time. See Keyboard accelerators, mnemonics, and shortcuts for more details.


38

Finally my original answer is completely obsolete. Google updated features with Alternating Colors features in "Format" menu item. There you can easily select a default scheme and customize as you like: Be sure to remove your old manual formatting, as this doesn't automatically replace/override. === OBSOLETE BELOW THIS LINE === Google added this feature ...


37

Ok, here is a modified version of the code in my previous answer: function onEdit(e) { // Your sheet params var sheetName = "MySheet"; var dateModifiedColumnIndex = 2; var dateModifiedColumnLetter = 'B'; var range = e.range; // range just edited var sheet = range.getSheet(); if (sheet.getName() !== sheetName) { return; } // If the ...


33

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


33

Assuming you want: Row | A | B | B's formula -------------------------------- 1 | 1 | 1 | =1 (or =A1) 2 | 2 | 3 | =1+2 (or =A2+B1) 3 | 3 | 6 | =1+2+3 (or =A3+B2) etc... Then you can use:=sum($A$1:A1) where A1 is the start of numbers to sum. Put that formula in B1 (or where ever you want) and fill it down, and it will sum all cells up ...


26

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


25

I've been able to do this with a Google Script like this. You can alter this to pinpoint the days you want to delete, etc. function delete_events() { var fromDate = new Date(2013,0,1,0,0,0); var toDate = new Date(2013,0,4,0,0,0); var calendarName = 'My Calendar'; // delete from Jan 1 to end of Jan 4, 2013 (for month 0 = Jan, 1 = Feb...) ...


24

Each sheet has its own unique URL. When you click into a different sheet, the URL in your browser's address bar changes. Save it as a bookmark. The sheets at the bottom of your google-sheets are draggable. You can simply drag your preferred sheet such that it becomes the left-most sheet. (This is good because if you access your sheet from an Android device, ...


23

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


21

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


20

The new Google Sheets allows you to do this, as described here. You have to first enable the new spreadsheets in your Google Driver settings as described in the article. Then you can select 'Custom formula is' from the conditional formatting options and enter any formula (do not forget to add the = prefix!). Cell references without $ prefixes are adjusted ...


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

I have found a way to do this, using the scripting functionality that Google Spreadsheet provides. Here's how to do it: open your spreadsheet in the menu, go to Tools -> Script Editor...; this will open a new window that allows you to enter code copy the code below paste the code in the Script Editor window and press CTRL+S to save it close the Script ...


17

=iferror(A1+B1+C1+D1+J1+K1+L1+"x",today()) whenever the cells in the row changes the formula re-calculates. with the "x" in the equation it guarantees an error. which in return, returns the date.


16

Short answer The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point. Below is a script that is easier to adopt. Instructions Create a test form and note the form ID (between ../...


16

There's is a shortcut to auto indent in GAS. The short-cut is Shift + Tab. Make sure you have all your code selected. As a best practice try to always use Ctrl + A to select all immediately before Shift + Tab This will change spacing in the whole document if all your code is selected.


14

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


14

Actually it is possible even without editing the HTML/CSS. I'll give you an example with a blank document but once you'll figure out how it works you will be able to do it on already existed document. Also if you are not already using the keyboard shortcuts I would suggest you to start using them (in this example I'm using Mac's shortcuts, but you can easily ...


13

I created a little script that converts the string literal "_now" to current datetime - function onEdit(e) { if (e.range.getValue() == "_now") { e.range.setValue(new Date()); } } I find it pretty handy to type _now and have it convert to current datetime value. See https://developers.google.com/apps-script/quickstart/macros and https://developers....


13

You can use A:Z or A:AB or A:XX (with XX being the last column of your page):


13

First of all, sorry for the late answer, but I have a solution for you to work with. Code function result(range) { var output2 = []; for(var i=0, iLen=range.length; i<iLen; i++) { var s = range[i][1].split("\n"); for(var j=0, jLen=s.length; j<jLen; j++) { var output1 = []; for(var k=0, kLen=range[0].length; k<kLen; k++)...


12

There are a couple of ways to do that. First, you need to declare the spreadsheet: var ss = SpreadsheetApp.getActiveSpreadsheet(); Secondly, declare the first sheet like this: var sh0 = ss.getSheets()[0]; or like this: var sh0 = ss.getSheetByName("Robin"); Then set the active cell and retrieve the column index: var editedCell = sh0.getActiveRange()....


12

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


12

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. (added search for static references as suggested by Graham below) You could add a similar function to the traceDependents function to search for the text within the active cell for a ...


12

Use the following code to change the date notation. Code var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy") References formatDate(date, timeZone, format) date notations


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