Tag Info

Hot answers tagged

6

Thanks to the wonders of floating-point arithmetics, ((0.6-0.3-0.4)+(-0.3+0.8-0.4)) evaluates to approximately -5.55e-17 in double precision. This holds in JavaScript as well as in Google Spreadsheets. It does not really matter whether the computation is client-side or server-side: what matters is the number format used. It appears that Google Sheets use ...


4

In the menu bar, click the words to the right of "Help": they say either "Last edit was ..." or "All changes saved in Drive". This opens the revision history of the document. You can choose a recent revision and restore it.


4

Google offers a limited amount of computing power to Sheets users. This restriction is particularly noticeable for scripts: they take a long time to run, and trying to invoke a script too often results in errors of the type you encounter. The task you are trying to do here could be done with a single script invocation. It may still take a while to process ...


3

Step 1: Open the form. Step 2: Click on "Responses" from toolbar. Step 3: Click on "Change response destination". Step 4: If you want to save responses in existing spreadsheet with a new sheet, choose "New sheet in an existing spreadsheet" or if you want to save responses to a new spreadsheet choose "New Spreadsheet" and "Type the name" for new ...


3

You can use a range as a parameter for the ISFORMULA() function. e.g. If you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1:J10) and apply it to the range A1:J10.


3

I've done a lot of experimentation. This is the easiest way to calculate a time delta in Google Spreadsheets. Format the cell containing the formula like this: Format > Number > More Formats > More date and time formats, delete "second" and :. Then, format the End time and Start time cells like this: h:mm am/pm. Use the formula =abs(end time - start ...


3

Short answer Use 1) the HTML Service of Google Apps Script or 2) update a cell formula with the IMAGE() fuction, any of them triggered by a Custom Menu as Google Docs editors doesn't include a mouse hovering event. Explanation Google spreadsheets and Google Apps Script (GAS) have had a lot of changes since the question was posted. One of them is that the ...


3

I found how to do it using vlookup. I have twos sheet. in one sheet I put keys and values 1 somtext 2 othertext 3 thirdtext In the second sheet =vlookup(a1,othersheet!a1:a3,2,false)


2

If setting the column as plain text doesn't work, try changing the format to Automatic. Click Format → Number → Automatic


2

Short answer Google Forms doesn't include a limit on the number of responses as built-in feature but you could extend the of Google Docs editors features though add-ons or Google Apps Script. Alternatives formLimiter From: https://sites.google.com/a/newvisions.org/scripts_resources/add-ons/formlimiter formLimiter automatically sets Google Forms to ...


2

At this time it's not possible from the Google Sheets built-in user interface to protect non-continuous ranges in single step. Instead do the procedure manually for each range. It's worth to say that it's possible to use Google Apps Script to automate certain tasks. References Protect a range or sheet from being edited - Docs editor Help Extend Google Docs, ...


2

Error The error you received has to do with the authentication of the script. Click on the bug button (marked yellow): A popup will appear and in this case you need to grant access to both the Google Spreadsheet and Google Contacts. It's also advisable to create an array to keep the results in, rather than writing each result to the spreadsheet. See my ...


2

Put all the data for all the months into ONE sheet. Use a column called Month to keep track of what month each row is for. When you want to see one month's data only, click in the data area, then choose Data > Filter from the menus. This puts a small drop-down arrow in the header cells, by clicking on this you can control what filters are applied. ...


2

This has nothing to do with ImportRange; the issue can be reproduced using Query within the same sheet. The documentation explains that Query only supports boolean, numeric (including date/time types) or string values. As far as Query is concerned, an image contained in a spreadsheet is simply a null value. A workaround is to store URL strings ...


2

Use =and(len(B2), ... ) where ... is your current condition. The length value of 0 corresponds to False; any positive value is True. A more explicit, but longer version is using not(isblank(B2)) instead of len(B2) By the way, your construction = if( condition, 1, 0) is not necessary; simple = condition works. E.g., =B2<((B$7/4)*$A2) is a valid formula ...


2

You can clear only the cells with orange background (color code #ff9900) by getting the background colors of all cells with .getBackgrounds() and then checking them for being orange. function clearOrange() { var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1'); var range = sheet.getDataRange(); var bgColors = range.getBackgrounds(); for ...


2

The formula =MAX(FILTER(A:A, B:B="something")) gives the maximum of the entries in the A column for which the corresponding entry in B column is "something". See documentation for FILTER.


2

The solution, as I understand it, is as follows. By setting a FILTER formula (array & condition) you create the first step, the "what are we looking for, specifically" then that formula gets wrapped in a SUM formula which adds the array values. So, in the case above =SUM(FILTER(B:B,A:A="June")) this formula is looking for the values, all of them, in ...


2

Once you are running code in the Sheet, you can loop through the column to see if the values you care about are set or not. You can then only send the email if your criteria are met. Keep in mind that if your logic is if (empty > 20) you will get an email on entry 20, 21, 22, etc. You may want to send one every 5 entries over 20 or something like that.


2

The easiest way to show a notification in Google Sheets is to use the method toast of the Spreadsheet object. Here's a simple example: function onOpen() { var message = 'The current time is ' + new Date().toString(); var title = 'Welcome to Google Sheets'; SpreadsheetApp.getActiveSpreadsheet().toast(message, title); } Upon a user opening the ...


2

Just put a concatenation formula into every row in column C. This is the formula to use if your values are text: =A2&B2 I'm assuming that if they were numbers, you would not just want to glue them together. But you can extend the concatenation, like this: =A2&"-"&B2 Here's a worked example, showing these formulas in use: ...


2

Use the Concatenate function to add strings together. In cell C1, put in =CONCATENATE(A1,B1) Then you can copy that result from C1, select the entire C column and paste it. So it should concatenate each row from column A and column B as you expect.


2

Depending on the details of your use case, one of the following may be preferable: Add a column with =ARRAYFORMULA(VALUE( B1:B100 )), assuming B1:B100 is the first output column. Then sort by this added column. This may be the easiest way to achieve the sort that you want. Apply Value only to the first output. To my understanding, this requires splitting ...


2

Short answer Don't use .getValue() on date/time cells. Instead, put =TO_TEXT(A1) somewhere (or already have A1 containing something like TO_TEXT(SUM(E2:E)), and use .getValue() to get that string. Numbers and strings pass between sheets and scripts reliably, unlike dates and times. Explanation Unlike the Unix operating systems for which the epoch begins ...


2

The arguments of COUNTIFS must alternate between ranges and conditions applied to those ranges. The condition "not blank" is expressed as "<>". (And "is blank" would be "=". In the context of this command, the lack of a thing to compare to is understood as comparing to empty string.) =COUNTIFS('Test Cases'!N3:N, "<>", ...


2

One big copy-and-paste from the Wikipedia list, and then delete the ones you don't want.


1

The filter in Google Sheets does not have this sort of tree selector for dates. Some alternatives: To display only the records from a particular year, select "Filter by condition" -> "Text ends with" -> 2014 (or some other year). To display only records within some period, select "Filter by condition" -> "Is between" -> start date, end date (for example, ...


1

You do not need OAuth for sending a simple mail from Google Script. You remove the deprecated part from your script and it should continue to work. function sendEmail(value) { var email = "ENTERYOUREMAILHERE"; var subject = "Vanguard Target Allocation Alert: " + value+"%"; var body = "This is an automatically generated email to inform you that your ...


1

This appears to require a script (Tools > Script editor) because the validation rules available through the interface don't seem to support "in this list but not in that list". Here's a script that assumes the options are in cells A1:F1 and the dropdowns are in cells A2:C2. (It is a bit easier to code this logic with row-oriented data.) Depending on your ...


1

Here is the function shuffleSheet which randomly shuffles the rows of the current sheet. The actual shuffling algorithm (which is classical) is taken from this answer by Laurens Holst. I imagine this is something you will not want to do on every edit, but only when the script is explicitly invoked. function shuffleSheet() { var ss = ...



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