Tag Info

Hot answers tagged

5

Solved with: =INDIRECT( ADDRESS( 4, COLUMN() ) ) ADDRESS gives me a string like "$A$1" and INDIRECT makes it a cell reference.


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


4

You can copy the chart and paste it into the sheet you want; then delete the chart sheet. The copy button is in the toolbar: Copying preserves the changes made to the chart.


3

If you don't want the header row with "Market Cap", tell the query command that the table has no header. The optional third parameter of query is the number of header rows. (When it's not provided, the spreadsheet will make a guess based on the appearance of the data, which is not always a correct guess). =query(importhtml(...), "where Col1 = 'Price/Sales ...


3

Short answer Google Sheets doesn't have meta-keys that disable cell navigation and referencing during the edition of a formula and this behaviour can't be disabled. Workarounds Add ) before pressing the left arrow key. i.e. if you write =SUMIF(, press ) so you have =SUMIF(). Now press the left arrow key. Press Backspace instead of the left arrow key. ...


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

As user3169 wrote, one can simply take the difference of time values (this works in Google Spreadsheets the same as in Excel). Specifically, the command =ROUND((F2-E2)*24) calculates the number of hours between the time values in the cells E2 and F2, rounded to the nearest whole hour. Similarly, =ROUND((F2-E2)*24*60) calculates the number of minutes ...


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.


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.


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


2

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


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

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

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.


2

You could use INSERT > Link... to add a link to the active cell but it will require you to write the URL. If you want that the URL be added automatically, you will need a Google Apps Script. Instead, you could use a formula with the HYPERLINK function in a temporary column and use some built-in functions to do bulk operations. I.E. if the search ...


2

Getting data by parsing the HTML source of a page is a fragile method: when the pages gets redesign, the import is most likely going to break down because the data you want are not where you expect. It's no coincidence that most of examples on Google's own IMPORTXML documentation page are broken by now. I recommend using Google Distance Matrix API ...


2

This is more complicated from your other question about ImportXML with XPath, because the table on the page with ?tab=recent is dynamically formed by a script. The table does not exist in the page source which ImportXML accesses. Inspecting the source, one can see the data embedded in a script: <script language="javascript"> ... "name":"Dota ...


2

You can do this with IMPORTXML. In A2: =IMPORTXML("http://steamcommunity.com/id/PerfectPlays", "//span[@class='actual_persona_name']") In B2: either =IMPORTXML("http://steamcommunity.com/id/PerfectPlays", "//div[@class='recentgame_quicklinks']/h2") which will return "108.9 hours past 2 weeks", or ...


2

Try: =ArrayFormula(if(len(H2:H)*len(G2:G), iferror(H2:H/G2:G, 0),)) The formula will output results if there are entries in col H AND col G. See if that works for you ?


2

Assuming your dates are in col A, try: =query(ArrayFormula(if(len(A2:A), {text(A2:A, "mmm"),if(E2:E<0, E2:E,0), if(E2:E>0, E2:E,0)},)), "select Col1, sum(Col2), sum(Col3) where Col1 <>'' group by Col1 label Col1 'Month', sum(Col2) 'Outgoing', sum(Col3) 'Incoming'") or, if you want an extra column with the 'gain': ...


2

A published Google spreadsheet is an iframe. At this time, Medium does not allow iframes (source). I think the best you can do to include a table is to Convert it into text (here's a handy tool for that). Post this text as a code block: see How to embed code snippets in Medium? Naturally, this copy-pasting will create a static snapshot of your ...


2

Google Query Language will return a JSON string. To get the cell value and format as you see in the spreadsheet, instead of /gviz/tq?tq= and the query string, use /pubhtml?gid= and the "right" parameters. The easiest way is to get the URL resulted of publishing the spreadsheet to the web. Then... Add range=A1 to the URL of the published spreadsheet, where ...


2

Yes, it's possible. But first, bear in mind that =CONCATENATE("a","b") is the same as ="a"&"b", but shorter. Example: =QUERY( IMPORTRANGE("key","A:Z"), "SELECT Col1, Col2, Col3 WHERE Col1 contains '"&B1&"' ORDER BY Col1" ) References CONCATENATE - Docs editor help


2

Try: =ArrayFormula(transpose(split(concatenate(A1:B&char(9)),char(9)))) (note that concatenate has a limit of 50000 characters) or =transpose(split(join(" ",query(transpose(A1:B),,50000))," ")) Where list A starts in A1, list B in B1.


2

You cannot send emails from the Google Sheet when an edit is made because the the onEdit trigger cannot call the MailApp service. You can however save the edit in a queue through PropertyService and then use a time-based trigger to flush the queue and send the content via email.


2

You need to put the blobs in an array. MailApp.sendEmail(emailAddress, subject, message, { name: 'Automatic Emailer Script', attachments: [attachment] });


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


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


1

Partial answer outline Simplified case Source data: 1 column, | (pipe character) as separator. Cell B1 formula: =Transpose(Split(Join("|",A1:A2),"|")) Formula Explanation First, join the cells values to make one list, using a character as separator. Second, separate the list elements at each separator. Third, change range from m rows and n columns to ...



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