Tag Info

New answers tagged

2

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


0

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


0

Here is one approach, which relies on using some character (backtick ` in my example) that is not contained in any header. I don't think I ever put backticks in any spreadsheets of mine but if you did, pick another character. D2 =REGEXEXTRACT(JOIN("`", FILTER(E$1:$1, LEN(E2:2))), "[^`]*$") (and drag down the column D). Explanation: FILTER selects the ...


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


1

There isn't an explicit "copy except blanks" command, but the following approach seems simple enough. For this illustration I assume the original data is in the rows 1-6, while the "scattered" data to be pasted there is in rows 7-12. Put the following, for example, in A14: =ARRAYFORMULA(IF(LEN(7:12),7:12,1:6)) This will fill the rows 14-19 with the ...


-1

The following piece of code makes it is possible to create different protected ranges throughout a spreadsheet at once. Code var app = SpreadsheetApp.getUi(); var ss = SpreadsheetApp.getActive(); function onOpen() { app.createMenu("Ranges") .addItem("Set ranges", "setRanges") .addItem("Delete ranges", "deleteRanges") .addToUi(); } function ...


0

This can be done with FILTER by MATCH: =FILTER(C2:C240,ISERROR(MATCH(C2:C240,B2:B240,0))) Here, MATCH returns an error if the element is not found in column B (the third argument, 0, requires exact match). The command ISERROR returns true in such a case, and this is what the column C is filtered on. In SQL, this would be implemented using, e.g., ...


0

No, there isn't. One alternative is to use Google Apps Script to create and open dialog box or custom side bar with the links. References Dialogs and Sidebars in Google Apps - Google Apps Script


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.


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


1

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


1

The button to change the text wrap option is slightly hidden. Hence, adding a screenshot from the new Google Sheets UI.


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


1

Sorry this is not possible yet in Google spreadsheet :(. But I am not sure if you have though of this solution yet; You could make a duplicate of both the spreadsheet b and a so that " the other people" can't see your historical data. Only issue with this is you would be remaking the whole thing:( Hope this help!


2

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


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.


1

You need a custom function contained in a script. For example, the output below is from entering B1 = hashtag(A1), etc, where hashtag is the custom function at the end of this post, placed in a script (go to Tools > Script Editor to paste it in). +---+-------------------------+-------------------------+ | | A | B ...


1

I figured it out by changing the Search Criteria from TEXT(NOW(), "yyyy-MM-dd") to TODAY()


0

Sorting a range (for example A and B columns) is a built-in function: Select the range to be sorted Go to Data>Sort Specify sorting criteria. To duplicate to another sheet, you have to first decide how wide the original ones are going to get. For example, suppose each sheet only uses five columns A:E. Then on Sheet8 you can enter A1 ...


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


1

According to Authorization for Google Services it's not possible because for Standalone, add-on, or bound to Docs, Sheets, or Forms the script run as the user at keyboard. The alternatives is to convert your script to a web app or to a Installable trigger.


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


1

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it). I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division. The formula for cumulative total is found here. I'll use it in this ...


0

Presumably, the image URLs will be found in the same column; otherwise you have bigger issues with your spreadsheet. Here is a script that converts the URLs from Column C to images. function convertToImage() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getDataRange(); var colnum = 3; ...


0

You could replace "CDI"&text(row(A:A),"000000") with text(month(now()),"00")&day(now())&year(now())&text(row(A:A),"000") but this would be the timestamp of now, not of when the cell was last updated. To capture the update time, you need a script. The script below runs every time the spreadsheet is edited, checks whether there are ...


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


0

Google Apps Script (GAS) Google spreadsheets could be used in a similar way that Macros are used in Excel. In the first reference below you could find how to start with GAS. The second reference was shared in a comment to the question that includes a script and example that could be used to start with. References Extend Google Docs, Sheets, and Forms ...


1

There is no way to block the download option of a shared Google spreadsheet. Instead save it as another file format or create a Web App. References Prevent viewers from downloading a file - Docs editors Help


0

I found this works quite well with the following provider: =importxml("http://freegeoip.net/xml/webapps.stackexchange.com", "//IP") or dynamically: =importxml("http://freegeoip.net/xml/" & A1,"//IP")


2

Apply to the headers (C1 and the following) conditional formatting based on custom formula =ISERROR(FILTER($B2:C,$B2:$B<>"Yes",C2:C<>"")) Here, FILTER selects the rows that satisfy both conditions: igredient is not in stock, and ingredient is used in the recipe. This command returns the error #N/A if no results are found. The command ISERROR ...


0

You can use Insert > Draw, create a text box and rotate it. Works for me.


2

In Conditional Formatting, select Custom Formula is and use the formula: =AND(ISBLANK(B4),ISBLANK(B5)) Then when both B4 and B5 are empty, they will both be coloured; when either one has text in it, they will both be white.


1

You can use =100*power(1.01, COLUMN(A:A)) (That is, replace 1 by A). Actually, =100*power(1.01, COLUMN(A:A)-1) would be better, as it allows you to apply the formula to all cells, including the very first one. Also, the answer I gave to your other question works equally well here: Put 100 in a cell (for example, B4), then enter in the cell to ...


0

You can use =SUM(FILTER(A2:Z2,ISERROR(DATEVALUE(A2:Z2)))) where DATEVALUE interprets the content as a date, if it would be auto-converted into a date when entered. Otherwise it throws an error. ISERROR returns True value if its input is an error FILTER narrows down the data set to those columns or rows where the condition holds.


0

This requires pretty deep diving. A script would have to check the existence of files to begin with, using the Drive app. Even invoking a script by a link inserted in a document takes a bit of coding; it's better to use custom menu commands for this. This is a kind of project that would make me think whether I'm using a right tool for the job. Google ...


1

Use the formula: =COUNTIF(UNIQUE(C:D),"Sales") How it works: Unique(C:D) will remove any rows from those columns that are duplicated, resulting in: Phone | Status -------|------- 1001 | Open 2002 | Sales 3003 | Sales The Countif portion looks inside those resulting duplicate values and counts every instance of "Sales".


0

Use the IMPORTDATA() built-in function. AFAIK the refresh of the data is done on the server side, so refreshing or recalculating the spreadsheet very frequently could not make any difference. The time that it takes on the server side is not documented in an canonical source; some sources say that it takes 5 minutes others 1 hour. References IMPORTDATA - ...


0

One alternative is to use a custom function with the getDataRange() method. From: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange() Note: The below quoted code isn't a complete custom function. To learn about custom functions go to the reference at the end. getDataRange() Returns a Range corresponding to the ...


1

Here is my answer from Stack Overflow: The unique() function gets rid of blank spaces, but wasn't helpful for me because some of my rows repeat. Instead I first filter the columns by len() to remove blank cells. Then I combine the columns together in the same way. ={filter(sheetone!A:A, len(sheetone!A:A)); filter(sheettwo!A:A, len(sheettwo!A:A))}


1

You can also use IMPORTRANGE in a conditional formula always TRUE : =if(now()>0;IMPORTRANGE("1IyAx_*****************************************Lza5x1iVfs"; "Masterlist!AT1:TA39");"CRASH") Plus you can (in spreadsheet parameters) add an update every minute. The IMPORTRANGE will be updated at least every minute.


1

Thank you! For the record, this is what I´ve done: =SUBSTITUTE(SUBSTITUTE(Results_ES!K5, "<h2>Especificaciones</h2> <p></p>", ""), "<table class=" & CHAR(34) & "generic" & CHAR(34) & ">", "<table class=" & CHAR(34) & "data-table" & CHAR(34) & " id=" & CHAR(34) & ...


1

You can keep only the part until the linebreak with the following REGEXEXTRACT command: =REGEXEXTRACT(Results_ES!C1, "^.*") If you also want to remove any spaces in your text before the linebreak, add TRIM: =TRIM(REGEXEXTRACT(Results_ES!C1, "^.*"))


2

String concatenation operator &, for example: "<table class=" & CHAR(34) & "generic" & CHAR(34) & ">" Or, the CONCATENATE function: CONCATENATE("<table class=",CHAR(34),"generic",CHAR(34),">")


1

In your code you wrote: var file = DriveApp.getFilesByName('test123.pdf') file is now an object containing all files with the name 'test123.pdf' The file in the object you want is file[0] In order to get the file you want, you can use MailApp.sendEmail(emailAddress, subject, message, { attachments: [file[0].getAs(MimeType.PDF)], })


5

This is a known issue with Google Spreadsheets, first reported a year ago. You can star the issue in the hope of increasing its importance. So far, the response from Google is Thank you for your report. We have been able to reproduce the problem and have filed it internally. Since getCharts is the only Sheet method returning embedded charts, Apps ...


1

Assuming that all your sheets are in the same spreadsheet and the range of interest is A:B where column B have values YES/NO and you want only the rows where B is equal to YES, then the following formula should work: =QUERY({ListA!A:B;ListB!A:B;ListC!A:B}, "select Col1, Col2 where Col2='YES'") References QUERY - Docs editors Help


2

Google official help file: The NOW function can be used to replace the GOOGLECLOCK function, which is not available in the new Google Sheets. Apply the NOW function and change your recalculation setting to "On change and every minute" in your spreadsheet at File > Spreadsheet settings. Source: https://support.google.com/docs/answer/3092981?hl=en


3

Without installing a Chrome extension, you can use these three key combinations to go to the next sheet on Chrome OS. Alt+Shift+K (Display list of sheets) up/down key (Highlight sheet) Enter (Go to sheet) Source.


0

In order for conditional formatting references to be remapped in the way you want, you should copy-paste a range of cell including the one being formatted and the one being referenced. In your case, with custom formula =J10<=J3, you can copy the range J3:J10 down to J16:J23, and as a result, the formatting in J23 will apply if the value in J23 is less ...



Top 50 recent answers are included