Tag Info

New answers tagged

0

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet. var range = sh0.getDataRange(); range.sort({column: 2, ascending: false});


2

This might be possible with some ingenious queries, but to me it seemed easier to write a script (entered via Tools > Script editor). It updates the sheet as new data is entered. Some explanation: The output is set to begin at cell F1, as in your example. This can be changed by changing the row number and column number 1, 6 near the end of the script. The ...


1

See if this works: function onEdit(e) { var s = e.source.getActiveSheet(), sheets = ['Sheet1', 'Sheet4', 'Sheet10'], nextCell = e.range.offset(0, 1); if (sheets.indexOf(s.getName()) === -1 || e.range.columnStart !== 13 || nextCell.getValue() !== '') return; nextCell.setValue(Utilities.formatDate(new Date(), "GMT", "HH:mm:ss")); }


1

As was stated in a comment, "That's not possible. The only way to do that, is to use Google Apps Script"


1

Instead of using a Google Sheet's custom functions, consider to use Fusion Tables. Also could be helpful to read Map your data with the Maps API and Fusion Tables - Google Geo Developers Blog Why? or regarding errors, Google Sheet's custom functions, purchasing a Google Apps for Work account... From Quotas for Google Services - Apps Script - Google ...


1

At this time Google Sheets doesn't have a way to protect cell format while allowing to edit its content. References How to share - Docs editors Help Protect a range or sheet from being edited - Docs editors Help


2

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)


1

This happens because your Google Sheets is linked to a Google Form. When you copy the sheet you also copy the form. You can create a new (not a copy) Google Sheet and copy the content from your old Google Sheet to it. Copying this new sheet will not create a new form. Please be more clear about the auto-creation feature you are talking about.


0

The simplest solution is to use a single sheet to register both member information and attendance. You could freeze the name columns and use FILTER, QUERY, Pivot Tables and other functions and tools to do reports and display data in convenient ways. If you still need or want to use two sheets, I suggest you to avoid to add rows in the middle of the data ...


3

For conditional formatting, apply the following to A2: "Custom formula is" =AND(F2>TODAY(),G2>TODAY(),H2>TODAY())=TRUE (You can then indicate what the range of formatting should be, e.g., A2:A to get all A column except the header). To filter the content, use either Query or Filter. Here is an example with FILTER: ...


0

Scripts can be invoked as custom functions (e.g., =myFunction() entered into a cell); but when used in this way, they can only affect the cell into which they're entered. Generally, the recommended way is to use custom menu commands (as I described, e.g., here). But if you really want a hyperlink, here is a way to do this. Warning: this is a kludge. Add ...


0

To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works: =filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31)) But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items 01 (January), 02 (February), ...


0

It seems you want the processed entries (the row with ~) to remain in the spreadsheet. In this case it's reasonable to have some indicator of whether the row has been processed by the script, so it doesn't add the same things again. Something like this: +---------+------------+---------------------------------------------+----------+-----------+ | Invoice ...


1

Add the following formula to the cell C1, and fill down as necessary =IF(A1="keyword",B1*2,B1) References IF - Google Editors Help


0

Yes, Google Forms & Sheets could be used together as simple database as the include several functions and tools that could help teachers to create grade reports. Actually is a very popular topic. Just take a look to https://www.google.com.mx/search?q=google+sheets+gradebook. It's worth to say that Google Forms & Sheets could even being extended with ...


1

I make extensive use of Forms and Sheets and it is really convenient for a number of reasons. One of my uses is for budget tracking and it's great because I have a shortcut to the form right on my phone homescreen and I can easily enter purchases and have all my sheets update. All of the built in capabilities such as sharing and the ability to script ...


2

Check out the built-in revision history, that's available in both the spreadsheet and the script editor under the first menu option (File). Read the Google Help for more info: revision history


1

Replace the last line of the first function, ss.insertSheet(sheetName, 4, {template: templateSheet}); with the following: var sheet = ss.insertSheet(sheetName, 4, {template: templateSheet}); sheet.getDataRange().copyTo(sheet.getDataRange(), {contentsOnly:true}); Here, the first line gets the reference to the inserted sheet. The second line uses ...


1

I found the easiest way was to put a simple if statement around the importrange. B1 = Sheet ID B2 = Sheet Name and Range B3 = Now() =if (B3> now()-1, IMPORTRANGE(B1,B2),) This works every time.


2

Use double instead of single quotations marks i.e. =IF(C1="NULL",B1,C1) References IF - Google Editors Help


2

I found a way to do it: Select Column A and "Insert 1 left" to add a column. Your table data is moved over one to columns B - D. In your newly blank Cell A2, enter formula =IF(COUNTIF($B$2:$B2,B2)=1, "Unique", "Duplicate") Copy the formula from A1 and paste it down the A column range for as far as you have data. The "$" absolute references will ensure ...


0

There is no way to securely block viewers or editors from getting viewing access to a range of cells. I.E. anyone having access to a spreadsheet could create a copy, this will make the copy creator the owner of it. One alternative is to use two spreadsheets, one for sensitive data and another for non-sensitive data each of them with the proper access ...


1

As far as I know, there isn't a way to do that. Instead, create a Web App so will have access to more options for your charts. One alternative is to use Google Apps Script HtmlService. Example Code.gs function doGet() { return HtmlService.createHtmlOutputFromFile('index') .setSandboxMode(HtmlService.SandboxMode.IFRAME); } index.html ...


4

Yes, and it's called RATE. Example of usage: =RATE(60,0,-100,150) calculates the percentage required for growing $100 to $150 over 60 interest payment periods, while taking out $0 each period. The answer is 0.68%. Wolfram Alpha agrees. The complete list of financial functions in Google Spreadsheets is here.


1

In the new Google Sheets some elements related to freeze/unfreeze columns & rows were updated, while others remain the same. I.E. The ten rows limit was removed. The gray handle, mentioned in the accepted answer is still there. References Freeze or unfreeze columns & rows - Docs Editors Help


3

Use conditional formatting with a custom formula. For example, select cell A1, go to Format/Conditional formatting, select "custom formula" and enter =ISERROR(VALUE(A1)) In the same dialog you can choose the range to which this formatting should be applied.


2

You probably want 1 instead of 16 as the last parameter of the query; this parameter means the number of header rows, not of headers. You also have an extra pair of parentheses in your formula. Otherwise the formula is correct (tested in my spreadsheet), although you could shorten it by replacing select Col1,Col2,..., Col16 with select * ...


1

Here's a script that you can tweak further for your use case. It adds a couple of custom new commands to the menu when the spreadsheet opens: The commands copy the content of the currently active sheet to the new one, named in the ISO date format, e.g., 2015-05-15. function onOpen() { var menu = [{name: "New Sheet as First", functionName: ...


1

You can add MATCH to your SORT for a custom order list.      The formula in D5 is, =ArrayFormula(sort(A$2:A$99,match(left($B$2:$B$99,3),{"sop";"con";"ten";"bas"},0),1)) Copy to E5. Without speed testing the calculation cycles it's debatable whether using the Left function to peel off the first three characters is seriously detrimental but it certainly ...


0

Unfortunately the history revision in Google Docs Editors, don't include a jump to the tracked changes, just a color highlighting and strikethrough for deletions. By the other hand, recently was launched a "suggestion mode". It inserts a comment automatically. They could be navigated through the comments panel. An alternative could be use Google Apps ...


0

As far as I know, at this time charts in Google Sheets don't include a datatable option. References Create a chart or graph - Docs Editors Help Editing and formatting your chart - Docs Editors Help


0

The values added to the right of QUERY() result aren't "tied" to the source range. To "tie" them, add these values to the source sheet, as was suggested in a comment posted by @pnuts.


2

Make a new sheet inside your current spreadsheet. Put two columns in it: VoiceType Sort order SOPRANO 1 CONTRALTO 2 TENOR 3 BASS 4 In your main worksheet (the one with the list of people), in a new column put a formula like this: =vlookup(upper(B2),Sheet2!A$2:B$5,2,0) Note that I have used the UPPER() function in case some of the ...


3

The following custom number format mask will produce the desired results without losing the accuracy of the underlying value. [>999999]0.0,,\M;[>999]0.0,\K;0       


4

No, that isn't currently possible. However, if the spreadsheet is used for viewing data (and not entering or modifying) you could make a new spreadsheet for each user, and use importRange from the "master" spreadsheet in each copy's "A1" cell. Each copy can then freeze/hide/filter as they please and their data will update almost in real-time as the master ...


2

I would use VLOOKUP instead of DGET; it has simpler syntax and works with ARRAYFORMULA. Example: Sheet2 columns A and B +---------+-------+ | User Id | Name | +---------+-------+ | 4444 | Grace | | 1111 | Jon | | 2222 | Tim | +---------+-------+ Another sheet has numbers 1111, 2222, 4444, 2222 in cells A2-A5. Entering in B2 the formula ...


0

While using nested IF statements works, the resulting formulas can be lengthy, error-prone, difficult to change or debug. For a complex pricing scheme I would use VLOOKUP. Example: the columns D and E contain the pricing policy +-------------------------+--------+ | If price is at least... | Markup | +-------------------------+--------+ | ...


0

You can extract the number to another column using REGEXEXTRACT, and then sort the range by that column. For example, suppose this is your C column: +-----------------------+ | 503 in Kitchen & Home | | 85.5 in Bed & Bath | | -263 in debt | | 633.26 in something | | 543 in Outdoors | +-----------------------+ Enter in column D ...


3

In cell G9 of the spreadsheet you shared, I entered this formula: =arrayformula(if(len(B9:B33)*len(C9:C33)*(C9:C33>B9:B33);if(timevalue(B9:B33)<timevalue("17.00.00"); mod(C9:C33-time(17;0;0);1)*1440; mod(C9:C33-B9:B33;1)*1440);)) It will only output a result when there are values in col B and col C and when col C > col B. See if this helps ?


1

The best solution for this is to put the following formula in cell A1 in Sheet2: =arrayformula('Form Responses 1'!C2:C) This will auto update all entries that are made in Column C on the first sheet. If you dislike the name of your first sheet, you cab just rename it of course!


0

Finally I have it working for different sheets in the same file: function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [] menuEntries.push({name: "Trace Dependents", functionName: "traceDependents"}); ss.addMenu("Detective", menuEntries); } function traceDependents(){ var dependentRefs = []; var ss = ...


2

You can refer to cells in the DSUM criterion. The problem with your query that you put a function inside of a string instead of appending its result to a string. That is, instead of ">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)" you need ...


1

As I mentioned in comments, this can't be done as you wanted because of the lack of programmatic access to filtered views (until this request gets implemented). So I wrote a script that does something along the lines that you wanted, but using the show/hide columns functionality. To simplify things, I am assuming that the data and the parameters are in ...


1

I would suggest using dget in the first sheet, if the number of users is defined, and doesn't grow automatically. Assuming the second sheet is Sheet2, I would add 2 columns to the first sheet. The first column would have cells with the formula (changing the cells accordingly, with copy and paste): =dget(Sheet2!$A:$C,"PAID ACCOUNT",{"USER ID";$A2}) The ...


0

By using array formulas with "literal" arrays you could append the three ranges in one. =ArrayFormula(Sheet1!A1:D4;{Sheet2!A1:C3,Shee2!D1:D3*-1};Sheet3!A1:D4}) Then you could add the balance column next to the resulting range.


0

Rather than a "Mail Merge" solution (one document for each record in a list), one alternative that could work for you use is something like UltraDox that include a feature to insert a spreadsheet into a Google document. I didn't tried it yet.


1

After entering 100 into one cell (say, A2), I would put the following into A3: =A2*1.01, and drag down this formula to fill more cells in the A column. (Although row() could be used for this, I prefer not to use it in formulas, to avoid them breaking when I add or remove the header row, etc.)


5

Introduce the POWER function and use the ROW function to increment the exponent. Your second line would be, =100*power(1.01, row(1:1)) Fill down and the ROW(1:1) will increment as 1, 2, 3, 4, 5, etc..


1

It is possible to assign a function to a button in Google Spreadsheets, but in practice it's easier to add commands to the spreadsheet menu. This way you do not have to look for them in the spreadsheet, and they do not get in the way of your data. Suppose you want custom commands "show/hide columns B-D". Create and save the following script in ...


0

Instead of using a script, consider to add the responses to another sheet using formulas and there add the three columns. It's worth to say that Today() column will be updated when the recalculation occurs. I.E. Adding a column with the formula =Today() to the responses of a form add the following formula to A1 in a new sheet: =ArrayFormula( { ...



Top 50 recent answers are included