Tag Info

New answers tagged

1

From Migrate to the New Google Sheets Custom function arguments must be deterministic Built-in spreadsheet functions that return a different result each time they calculate — such as NOW() or RAND() — are not allowed as arguments to a custom function in the new version of Sheets. If a custom function tries to return a value based on a volatile ...


0

At this time, there is no way to limit a user to send a response, but you could allow only one response per user. See View and manage users responses


0

At this time ImportRange only imports data; it doesn't import formatting. An alternative is to copy the sheet and move it to another spreadsheet.


0

At this time range & sheet protection of Google Sheets doesn't protect formating.


2

The following little script limits the cell input to 5 positions and adds the surplus as a note to the cell. Code function onEdit(e) { if(e.value.length > 5) { var c = e.source.getActiveCell(), v = e.value; c.setValue(v.substring(0,5) + "..."); c.setNote("Remaining text was: \n....." + v.substring(5)); } } Explained On each and every ...


0

Protect the sheets of your spreadsheet, and add people that you want to see the change history as collaborators. See Protect a range or sheet from being edited for details.


-1

Only the older spreadsheets that were not been migrated yet could be published as RSS. It's worth to say that older spreadsheets will be migrated over 2015. See Upgrading older spreadsheets to new Google Sheets for further details.


2

Use a Data Validation with a custom formula. If the cell to limit the value length is A1 and the length limit is 5, then the custom formula is =LEN(A1)<=5 References Data Validation - Docs Editors Help


1

At this time it's not possible because Google Docs doesn't have built-in features to embed external content. There are features to insert external content like the Insert menu, the Research tool, and add-ons but they are not able to "link" content. The insert an image, insert a drawing, insert a copy of the selected content; the research tool could insert ...


0

Yes. The form owner should set the form to allow users to edit responses. This will give them a link that they could share with others. For more details see Edit form responses after submitting - Docs editors help.


2

In Google Spreadsheets, you can use the UNIQUE() formula to do that. Formula =UNIQUE(A1:A10) Explained For the range as seen in the screenshot, there are 10 entries. The UNIQUE() formula accepts a range and filters out the duplicates and returns that range, see screenshot, leaving only 7 unique entries. Screenshot Reference Google Spreadsheets ...


0

I wasn't quite sure what you meant by Range as an input to your custom function. Is this the source range or destination range? Anyways, this is something that should hopefully get you started. There is no timer element, nor is it a custom function. But can be converted to one easily. The manual timer element can be achieved by adding a trigger. In script ...


1

Prepend the column letter and/or row number that you don't want to change with $. Let me illustrate: Let's say in cell B2 I have this formula =B1*A1 If I copy that to C2, what is pasted in C2 is =C1*B1 But that's not what I want. I always want to multiply against A1. If, instead, B2 was =B1*$A$1 Then when I copied to C2 I'd get =C1*$A$1 If I ...


2

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...


2

This (presumable) oversight has since been corrected: here is the current screenshot of publishing a new Google Sheets document.


1

A rectangular box A fits inside of box B exactly when all of the following apply: The largest dimension of A <= the largest dimension of B The middle dimension of A <= the middle dimension of B The smallest dimension of A <= the smallest dimension of B I used MAX, MIN, and SMALL below to implement this check. ...


2

This is a job for regexreplace. Suppose A1 contains the text Membership and Volunteer Crew - do you like getting to know lots of new people? | Sauna Crew - Spend time sweating and supervising our sauna activity. | Crafters Crew - find and invite and present regional Crafts People at R&S and help them present workshops to our audience. Put the ...


0

Surely CTRL + F works. Find and replace. Search for - replace with ::: Search for | replace with ::: alternatively a formula would work. =SUBSTITUTE(A1,"-",":::") =SUBSTITUTE(A1,"|",":::") The first replaces the - The second replaces any | IT assumes your data is in A1 and can be dragged down


3

The problem was due to Google Forms adding a new row every time. If you are having the same problem here's what you need to do to fix it: Where you previously had your formula change it to: =arrayformula('YOUR SHEET NAME'!A2:A) Change the sheet name, and enter the cell you want to target, as-well as the column. Make sure you delete all other formulas ...


1

If you have multiple sums in the formula, you need multiple SUM commands, one for each sum. That is, =F15*20+SUM(F16:F20)*25+SUM(F21:F23)*30 Have to add things up before multiplying them all by a common factor.


0

This is not a very elegant solution, but at least it does not require a script. Enter the formula =IF(F2="","",IF(2*F2=SUM(F$2:F2),"",SUM(F$2:F2))) in the cell F3. Drag it down throughout the column. Right now, the column is empty. But if you enter 100 in F2, the number 100 will appear in F3, as the total. If you type 300 in F3, the number 400 will ...


1

I think this has to be done with a script, i.e., via Tools->Script Editor. Here's the script content: function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getDataRange(); var values = range.getValues(); for (var i = values.length; i>=1; i--) { if ...


3

Google has added a new number format called Duration. Set your start and end fields to Format -> Number -> Time and your calculation field to Format -> Number -> Duration Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.


0

If selecting some cells in the spreadsheet: 1. Hold the ctrl key down. 2. Select the cells that you want to empty. 3. Once cells are selected, hit the backspace key. If selecting all cells in the Spreadsheet: 1. Press Ctrl + a 2. Once all cells are selected, hit the backspace key.


1

The following comes from Jimmy in this Web Apps answer. In both spreadsheets insert an =now() equation in a random cell, say Z1 In both spreadsheets insert an =importrange() function that references the now function of the other spreadsheet. Go into your spreadsheet settings and choose to recalculate on every minute. I tried a lot of other suggestions ...


1

There's no way, that I am aware of, to combine an image and some text in the same cell using the image function. The concat method concatenates strings, only. The only "workaround" I can think of, is inserting the image using Insert → Image, and positioning the image next to a text cell. But this is obviously not a perfect solution in any way.


0

All is considered a string when you concatenate elements. Therefore you need to format the text as a date. The following formula does that. Formula ="Happy " & TEXT(B2, "dd/MM/yyyy") Reference Google Spreadsheet help, TEXT()


0

Try the following modification. Formula =QUERY(DATA, "SELECT B WHERE A='Exit' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ") Reference Google Spreadsheet: Count values from last 30 days


1

I think I understand your question. Have you tried using the search and replace feature? I believe there's an add on for that if sheets doesn't have it by default.


1

For the cell you could also use this formula. =IF(F2 <> "No", B2, "") For the complete column you can use this formula. =ARRAYFORMULA(IF(F2:F <> "No", B2:B, ""))


1

You're looking for the IF command, combined with NE (not equal): IF(NE(RedCellReference,"No"), LeftCellReference, "")


-1

Sorry I took some time to build the spreadsheet. If you don't know too well how to handle pivot table reports, this spreadsheet I created may help you learn and like to use pivot table reports: Google Spreadsheets: Identifying and Working with Any Cells that Match


0

Create a Named Range in the first sheet. Then refer to those cells in the other sheet by the named range. The named range will refer to those cells even if you insert/delete other rows or columns before it, thus changing their cell numbers.The named range will expand if you insert rows or columns within that range.


0

Convert your csv data into tsv (tab separated values). Paste it then.


1

This script will do the trick: /** * Replaces some spaces with newlines in str, specifically, ' a.' becomes '\na.' for any letter 'a'. * See http://webapps.stackexchange.com/questions/76035/how-to-add-line-breaks-in-a-cell-before-specific-keywords-in-google-sheets */ function InsertLineBreaksInString(str) { return typeof(str) === 'string' ? ...


1

This is more a workaround than a real solution, but add this to the scripteditor and change to sheet name.. function onEdit(e) { if (e.source.getActiveSheet() .getName() !== 'Sheet2' || e.range.columnStart !== 7) return; e.range.setFormula("=rept(char(9734)," + e.value + ")") } Since I do not know how to work with a star symbol (char) in GAS, the ...


2

Yes - there is a small dropdown menu in the top right corner of the comments menu: Click the Delete menu item, and the comment thread is gone.


2

This little code makes it possible to create shortened URL's, with the use of Google Apps Script and the URL Shortener API. Prerequisites Under resources in the script editor (Tools > Script editor) select the Advanced Google Services and activate the UrlShortener: Then go to the Google Developers Console and select the URL for the API: Enable the API ...


3

Coercion in js is amazing and in this case you could simply use like this sheet.getRange("B2").setValue(A + "") Simply plus the empty string to your value. And for something in the similar but reverse way you can use +myString Just using an unary + operator this will coerce the string to a number. Find more about js coercion with a super sensei ...


1

Try =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where Col18='"&E3&"' and Col19='Installation Booked' ")


2

Try: =query({A:A, A:A}, "Select Col1, count(Col2) where Col1 <>''group by Col1") or if you don't want the header 'count': =query({A:A, A:A}, "Select Col1, count(Col2) where Col1 <>''group by Col1 label count(Col2)''") An alternative way would be: =ArrayFormula({unique(A1:A6),countif(A1:A6, unique(A1:A6))})


0

You might check out Cloudward's Cloud Snippets. They have a snippet that allows you to publish a Google Sheet as a list to the web. If you know some HTML/CSS you can further customize before publishing. They have lots of Snippets to check out. Here's one that might help: ...


1

Try: =ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9)))) or: =ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9)))) or also: =ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))


1

Check out this formula I entered in G2 of the sheet 'SHEETJP' in the spreadsheet you shared: =ArrayFormula(vlookup(query({row(A2:A),sort(A2:E)},"select max(Col1) where Col2 is not null group by Col2 label max(Col1)''",0),{row(A2:A),sort(A2:E)},{2,3,4,5,6},0))


1

.. and for the first question, assuming your data start in row 2, in C2 try: =ArrayFormula(if(len(A2:A)*len(B2:B), B2:B-A2:A,)) This formula should automaticcaly output the difference between B and A when data is entered. See if that works ?


2

Assuming you have a new line character in every cell and you want to extract everything before the new line, try: =ArrayFormula(iferror(regexextract(A2:A, "^(.+)\n"))) (where A is the column with the addresses and your data starts in row 2 --> change to suit). After that, you can copy the output and use 'paste special' (values only) to overwrite to ...


1

Im going to go with the assumption that what's happening here is not your connection. This is actually caused by Google servers not allocating enough resource to your requests. The reason the calls are so long is because the server is processing them so it waits for the data to return in whole before it stops. If it doesn't get enough data in x amount of ...


2

This formula will work to keep blank cells from returning "0" to another cell. =IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero") Then, you can just remove the dontsayzero after you've tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more "IF" and "OR" statements. Check this one out (remove carriage ...


0

With the following little piece of code you can increment any number by 1 (or whatever you choose). Code function onEdit(e) { if(e.value == "_incr") { var v = e.range.offset(-1,0).getValue(); if(typeof v == "number") { e.range.setValue(v+1); } } } Screenshot Explained The onEdit trigger is only executed when the input ...


3

In addition to previous contributions, this should also work: =sumproduct({$C$3:$C$9; $F$3:$F$9}=A12, {$B$3:$B$9; $E$3:$E$9}) and drag down. The first literal array {} makes one column out of the C-range and the F-range. The second one does the same with the B-range and the E-range. When the 'first column' equals the contents of cell A12, the 'second' ...



Top 50 recent answers are included