Tag Info

New answers tagged

1

You can achieve the effect of CLAMP(value, min, max) with =IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max) which is compatible with ARRAYFORMULA and uses value only once. Explanation: SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise. IFERROR returns the first argument, unless an error occurred in it, ...


1

To join while ignoring nonempty cells, use =JOIN("~", FILTER(B3:E3, LEN(B3:E3))) The command FILTER selects only the entries with nonzero value of LEN, i.e., nonempty one. For non-contiguous input range, such as g3, f3, h3, i3, you can use {array} syntax: =JOIN("~", FILTER({g3, f3, h3, i3}, LEN({g3, f3, h3, i3}))) Just for fun (you wouldn't want to ...


0

Yes, this is what VLOOKUP does: it looks up an entry in a given range, in vertical direction. For example, =VLOOKUP(A2, Sheet2!A2:B, 2, FALSE) would be a formula for the second row in Sheet1: it searches column A of the second sheet for whatever name is in A2, and returns the corresponding value in B column of Sheet2. If some names appear more than ...


1

In Google Spreadsheets, a date always refers to some year, although the year need not be shown. Otherwise it's not a date but a string. A string is stored as is, but you can't do any computations with it, and sorting will be alphabetical rather than chronological. If you prefer to keep 25/4 as a string Prior to posting data, format the column for the date ...


1

At this time, the LOOKUP function is available in the New Google Sheets.


1

At this time right click shows a context menu that include paste as an option. Just do right click over an editable area in a document, spreadsheet or presentation.


0

Without a script First, fill column A of the second sheet with =IF(TODAY()-ROW()+2>=DATE(2015,1,1), TODAY()-ROW()+2, "") where DATE(...) is the date that your records begin. This creates a reverse-chronological list of dates, beginning with today. It will be automatically updated as TODAY() changes. As any non-script solution, this approach is ...


0

I have just hit this problem, but unfortunately I don't know the number of digits I'm expecting so I don't know how many zeros may be missing. Luckily I thought of this before I sent the form out, if you already have responses then this won't help (unless you send it out again). To solve this problem I have: Set the question type to "Text" Under "Advanced ...


3

Unfortunately, this is actually an issue on Google's end. There is a limit of 400,000 cells, (source) or 2 million cells (for the new Google Sheets) per spreadsheet. (source). When IFTTT reaches this limit it makes a new spreadsheet. One temporary way to get around this limit is by deleting the columns you don't need (for example columns E onwards). This ...


0

Apparently, this requires copy-pasting: Delete the block Copy the data below it Paste it to the empty space. (The first step can be skipped, although this may increase the risk of pasting to a wrong position.) If you find this tedious after a while (as I did), a script can do the same. The script below adds a menu item "custom / "delete and shift ...


2

Short answer Don't do batch Google searches. Explanation Doing automated queries could block you, your equipment, network, your app/script, etc. from getting results from Google Search, so instead of usign Google Search use the Google Apps Script Maps service. See Tutorial: Getting to Know the Maps Service to learn more. References "Unusual traffic from ...


2

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


1

Short Answer Use arrays in Google Sheets and SORT() function. Example To combine the non continuous ranges A1:D5, H1:K100, P1:S15, the following formula will return a 4 X 120 array sorted by the first column in ascending order. =SORT({A1:D5;H1:K100;P1:S15},1,TRUE) The first parameter also could be any function that returns an array like IMPORTFEED. ...


1

Short answer Replace Renewals!B:C by Renewals!A:J Explanation The problem is that the second parameter of the QUERY() function is doing references to column 10 while the columns specified by the range in the IMPORTRANGE() function only include two. The solution could be 1. To change the range parameter of the IMPORTRANGE() to include enough columns. 2. ...


1

if (value >= "B2") ... That's just comparing value to the string "B2". You wanted to compare to the content of the cell B2. That is, if (value >= sheet.getRange("B2").getValue()) ...


1

Short answer It's not possible to block a range from be edited by the owner of the spreadsheet. Alternatives Transfer the ownership of the file to one of your parents and let them set protection for the columns. Use another spreadsheet owned by one of your parents make them share it with you with view access and import the column using IMPORTRANGE(). ...


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


0

Introduction Before I started to use Google spreadsheets I a similar situation happened to me with Excel. At that time I learned that when exporting or saving in another format a large number of rows, Excel takes the first 10 rows to infer the format of the whole column. To do this, Excel needs that the cells are not blank nor empty, otherwise the default ...


-2

Try this link below https://support.google.com/docs/answer/58183?hl=en This should help you resize the columns and rows.


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

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


1

Short answer Instead of =TRANSPOSE(TRANSPOSE(A1:A30)) consider to use ={A1:A30} as it's shorter. Long answer An alternative to ARRAYFORMULA is the use of embedded arrays also named inline arrays literal arrays array parameters "your own arrays" Example of embedded array of constants ={"A","B";1,2} ={A1:B2;D1:E2;G1:H2} Example of embedded array ...


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


0

I found a simple hack to simulate a manual cell update and launch modifications in an external spreadsheet. 1) Created a custom function like this: // Hack function used just to simulate a manual update function hack(value) { var list = [] list.push(value); return list; } 2) Then, on my sheet I call it in this way: =arrayformula(hack(tab!B1)) ...


1

In principle, this could be done without a script... but it's really ugly. Already looking up the nearest nonempty cells (up and down) is nontrivial (and slow), and it gets much worse when you start writing data into the column, thus changing what's empty or not (circular dependency). One could calculate in another column and then paste the values... but ...


1

To find the approximate usage, say, in June 2015, I would look at the last reading before this month began and at the first reading after it ended; then pro-rate the use accordingly. This guarantees the usage estimate will be based on an interval that's at least a month long. I used the Match command to determine the relevant rows for each month, then ...


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


0

Assuming the column is B for the purpose of this example, here's a function that fills it with "yes": function yes() { var ss = SpreadsheetApp.openById( ... ); // see below var sheet = ss.getSheetByName("Sheet1"); // the name of the sheet var range = sheet.getRange("B1:B"); // or another column var numRows = range.getHeight(); var values = ...


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


0

Ok, so I have solved my problem. I have absolutely no clue why there was an issue, and why my fix worked. All I did was enter the advanced setting of the Chart, and reduce the range of the data I was using. Basically, reducing the range from A1:F300 to A1:F51 was enough. Once I did this, the exported chart in my GoogleApp started being compliant to the one ...


1

The command IMPORTXML ignores the nodes with no text content: for example, taking //div[3] from the document where the body consists of <div>First</div> <div>Second</div> <div></div> <div>Fourth</div> results in "Fourth". So, when you count the <div> elements, skip over those where there is no text. ...


0

The following script sorts parts of the active sheet by the first column. The parts are separated by blank entries in the first column. So, for example +-------+ | Title | +-------+ | sss | | bbb | | | | eee | | wwww | | ccc | +-------+ becomes +-------+ | Title | +-------+ | bbb | | sss | | | | ccc | | eee | | wwww | ...


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


1

Short Answer Google Sheets is a very powerful tool that have several alternatives to achieve a result similar to UNION-SELECT. Alternatives Embedded arrays An alternative to use SPLIT-TRANSPOSE-JOIN-CHAR way is to use embedded arrays. One advantage of this method is that it doesn't require to construct/deconstruct strings, so the limit of the number of ...


1

I offer two versions, depending on whether blank cells should be kept. The basic idea is the same: Pick a character that is not contained in your table (e.g., an exotic Unicode character accessible via CHAR). Use it in the JOIN command for each of three columns. Concatenate the results, adding the character in between. SPLIT the concatenated string by the ...


0

I don't think there is a way to do this using the methods of ContactsApp in Google Apps Script. But you can use Google+ API to search, e.g., by email address; this can be done in any language, including JavaScript (the documentation page has sample code and a form you can use to try out this search, so I'm not giving details here). This will only find ...


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

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


1

Dialogs and Sidebars in Google Apps Scripts that are bound to Google Docs, Sheets, or Forms can display several types of user-interface elements — pre-built alerts and prompts, plus dialogs and sidebars that contain custom HTML service or UI service pages. Typically, these elements are opened from menu items. (Note that in Google Forms, ...


0

The last update made it possible to print comments in Google Spreadsheet... but that's even a bigger problem.. you can't switch it off. So all the comments are always printed... to paper, when you save as excel, save as pdf. I see no other possibility than to remove all the comments.


0

Another option is to sort your spreadsheet by date, and then select (click and drag) the rows for the range of dates you want and then create the pivot table. The pivot table will then just use the data range you selected on the spreadsheet.


1

As an alternative to the use of a custom function, consider the use of built-in functions like vlookup(). From the previous linked help article:


1

This is what the command VLOOKUP is for. Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here, A1 is the entry to search for Sheet2!A:C is the range in which the first column (A) is to be searched 3 is the column number from which to take the result (that is, C) FALSE requires exact match, and does not assume the keys are sorted in ...


1

At this time is not possible to set "revision points". Instead, try the following: Create a Copy of the spreadsheet Edit it When finished, copy it over the original References See the history of changes made to a file - Docs editors Help


0

Copy the dates that are interpreted as text values Paste as values Once the values are identified as dates, use the date formatting to display the dates as desired Example


1

Several issues here. When using query with importrange, the imported columns must be referred to as Col1, Col2, Col3, etc, according to their positions in the imported range. Your reference "ET" will not work. You have a mess of quotation marks going on. Remember that the query string can contain single quotes. Instead of "WHERE ET>=date ...


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

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


0

If you never skip a date, an easy approach is to color based on custom formula =ISODD($A2) (assuming the date is in column A), applying this formula to the range such as A2:Z100. The reason this works is that the date is in fact just the number of days since the last day of 1899. This value being even or odd alternates between days. The command =ISEVEN($A2) ...


1

To prevent entering a date in B2 that precedes the date in A2, apply the following Data Validation rule to B2: Custom Formula = B2>=A2 In the options, select "reject input on invalid data".



Top 50 recent answers are included