New answers tagged

1

The following setup will return the last price for NASDAQ 100 Index (NASDAQ Calculation) A1: http://bigcharts.marketwatch.com/markets/indexes.asp A2: =//tr[contains(.,'NASDAQ 100 Index (NASDAQ Calculation)')]/td[4] A3 Formula: =IMPORTXML(A1,A2) A3 Result: 4,512.54


1

You can freeze the row(s) with column headers, using the menu: View > Freeze > 1 row (or another number). The frozen rows are not included when executing "Sort sheet A-Z" or "Sort sheet Z-A" from the expandable menu next to column names, or when sorting the sheet from "Data" submenu.


1

Short answer AFAIK, regarding XPath queries to be used with IMPORTXML there isn't straightforward method as XPath 1.0 support looks that was not fully implemented and the web pages developers could follow the practices to set the structure of their webpages. Explanation While the use of tools like Chrome Developer Tools or browser extensions/add-ons could ...


0

One way to do this, assuming the page is rendered is to use a plugin or add-on to generate it for you. For example, if you use Firefox use this link. https://addons.mozilla.org/en-US/firefox/addon/xpath-checker/ or this using Firebug which is another add-on with this http://www.wikihow.com/Find-XPath-Using-Firebug


2

I solved this by using a simple query function (I added it as an edit in my question originally): query(Sheet1!$A$2:$C7,"Select C Where (A="&A2&")",-1) This basically queries Sheet1 Range A2:C7 with the value of column A in Sheet1 matching A2 ("&A2&") in Sheet2 and wherever it matches in column A, it selects the value from column C. Also ...


0

The advised function below has been added to a Google sheet file to advance to the next worksheet every minute by a defined trigger (as a test for an eventual daily trigger). However, the advance to the next worksheet only happens when I click the Run button from the script editor and no error email messages are received. Does the name of the project ...


1

Although the documentation never says so explicitly, custom functions cannot be used in conditional formatting. A workaround is to add another column with the output of the custom function (preferably optimized to handle an array at once), and then use that column in conditional formatting rules.


0

One approach is to put the following in cell Sheet2!B2, dragging it down the column. =iferror(indirect("Sheet1!A" & min(arrayformula(if(Sheet1!A$2:J = A2, row(Sheet1!A$2:J), )))), "no match") It will return the first entry in Sheet1's column A for which the row contains a value in Sheet2!A2. If no match, it returns "no match". Same can be done for ...


0

You can achieve this with one formula entered in cell A2: =arrayformula(date(2016,5,21) + 7*(row(A2:G)-2) + column(A2:G)-1) Here, date(2016,5,21) is the date you want to begin your calendar with. The term 7*(row(A2:G)-2) increments the date by 7 for each row after the 2nd one (since you begin with A2). The term column(A2:G)-1 increments by 1 for each ...


0

I tried asking the same question on Google Product Forums and someone posted this script. It works like a charm. Hope you guys find it useful. /** * Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 3. */ function onEdit(e) { var ss = e.source; var s = ss.getActiveSheet(); var r ...


0

Here is an Apps Script function that switches to the next sheet, one after the currently open sheet. When the active sheet is the last one, it will loop back to the first. function nextSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var numSheets = ss.getSheets().length; // number of sheets var activeSheetIndex = ...


1

Apply conditional formatting with custom formula =arrayformula(sum(N(not(isnumber(A3:A)))))=0 This can be applied even to a range of headers such as A1:E1; the formula, entered as above, will be interpreted in a relative way for other columns. Explanation not(isnumber(...)) is true if the entry is not a number N converts logical true/false to integers ...


0

The way I managed to do this, even though it will be very cumbersome if you have multiple rows that you need to check, is by using the following equation coupled with the Conditional Formatting feature (Format > Conditional Formatting): =IF(AND (ISNUMBER(n), ISNUMBER(n1), ISNUMBER(n2)), True, False) Where "n" is whatever cell you're checking.


2

This is probably best handled by a custom function. The function I wrote takes the data range (including the header row) as an argument and outputs the new table, with Ids and its header. Usage example: =inOut(A1:C20). It also checks data for consistency: the times must begin with In and then alternate between In and Out. function inOut(arr) { var data = ...


2

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service. Example: Adaptation of the code taken from the answer by Vidar S. Ramdal function parseFeedXml(url) { var result = []; var xml = UrlFetchApp.fetch(url).getContentText(); var document = ...


4

You can create a trigger that runs every time your spreadsheet is opened. Go to Tools → Script editor and paste the following: function onOpen(e) { var spreadsheet = e.source; var sheet = spreadsheet.getActiveSheet(); var lastRow = spreadsheet.getLastRow(); if (sheet.getMaxRows() == lastRow) { sheet.appendRow([""]); } lastRow = lastRow ...


0

Assuming the columns you have are C-G, entering =arrayformula({C2:C, D2:D & E2:E & F2:F, G2:G}) in the cell H2 will have the desired effect. The arrayformula indicates that in every row, one should have three entries: one from C, one is concatenation of D,E,F, and the last from G.


1

function onEdit(f) { if (f.range.getColumn() == 6 && f.value == "A" || f.value == "B") { f.range.getSheet().hideRows(f.range.getRow()); } }


0

You need a script for this. This script runs on every edit (onEdit), checks whether the edit is in the 5th column (E), and if the new value is 1, then it hides the row of that cell. function onEdit(e) { if (e.range.getColumn() == 5 && e.value == 1) { e.range.getSheet().hideRows(e.range.getRow()); } }


1

Such locking is not possible. Users can always choose to copy one cell and not the other. Alternative suggestion: combine data A1-B1 in one cell A1, separating it by a linebreak or some other character that will not appear in the data itself. Then it will be copied as a whole. When you need to use it, you can apply split(..., char(10)) to split the ...


3

You can do this with the indirect formula: =ARRAYFORMULA(INDIRECT("'data("&A1&")'!A:A"))


4

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. So, you should replace "select D where V = 1" with "select Col4 where Col22 = 1" The reason is that imported range is not considered a part of any sheet, so its column names are not like sheet ...


-1


0

try this: =REGEXEXTRACT(B1,"\d+\.?\d+$")*1+REGEXEXTRACT(C1,"\d+\.?\d+$")*1 REGEXEXTRACT part of formula "\d+.?\d+$" does this: \d - match and retun digit from 0 to 9 \d+ - gives one or more digits . - any symbol \. - dot ? - makes previous symbol optional (we use it here to match integers) $ - matches end of string


1

From today (2016-05-20) on, Google is starting to roll out the ability to embed a chart from a Google spreadsheet into a Google document. At this point, two of my 3 Google accounts make the option available in the Insert menu, with a new Insert→Chart submenu below the Insert→Drawing… item. When you insert such charts, they remain linked to the original ...


0

Here is how this can be done. The function sync should be set to trigger on edit (via Resources > Current project's triggers). The arrays in the first two lines should be filled with sheet names and cell names that you want to sync. If the edited cell matches one of those, then the rest will be updated to have the same value. function sync(e) { var ...


2

As Rubén said, conditional formatting is for human consumption only: neither sheet formulas nor scripts can access it. You should create a column that records the fact of "all answers are correct", and then filter by that column. For example, put in E2 =arrayformula((C2:C=2)*(D2:D=4)) which will put 1 in every row where both answers are correct. Then ...


0

importrange is not going to reorder any rows. It only imports data. The root cause is poor data structure: the second spreadsheet (with expenses) does not carry any information on whose expenses are those. This should not be acceptable data management: each table must have a column that can be used as an identifier. (Ideally, a numeric Id of an employee, ...


5

The following script will give you the count in the Logger log. 776 in my case. Which was interesting to know. function findOut() { var docs = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS); var i = 0; while (docs.hasNext()) { var doc = docs.next(); i++; } Logger.log(i); } This script will list all the names. You could write the data to a ...


2

This is now a built-in feature in Google Docs: see Add a chart to a document. In the menu of your Google Document, go to Insert > Chart > From Sheets Select the spreadsheet and a chart in it. Keep "Link to spreadsheet" checked, if you want this to be linked to the spreadsheet. The linked charts are not updated automatically: instead, a floating button ...


1

The formula =A1+((A1*25)/100) can be written as =A1*1.25 Multiplying a number by 1.25 several times means multiplying it by a power of 1.25. For example, =A1*1.25^10 returns 3539.025784, as you want. In general, your formula could be like =A1*1.25^B1 where A1 has the original value and B1 has the number of upgrades.


0

It is possible to give specific users permission to edit specific ranges. To do this, select a range, choose "protect range" from context menu, then click "set permissions". You will need to know the email addresses of the users, and with many users this is going to be a lot of hassle. Also, this doesn't look much like "online calculator". To collect data ...


1

To return only one row, use limit 1 in the query; this clause appears before label: select C, D, ... where B='"&A35&"' limit 1 label C'', D''... The default order of the rows returned by query is the order in which they appear in the sheet, so this will result in the first row being returned. Alternative solution: use filter instead. The ...


4

You can, but it involves tricking My Maps. What you essentially have to do is load the extra spaces you might need later. In your original spreadsheet, at the bottom of your table, put a space inside each cell across the width of the table. Select and copy that new "empty" (wink wink) row, and past as many rows as you think you'll need in the future. Save ...


1

This custom function can be reduced to: function multipass(text, ChangeFrom, ChangeTo) { ChangeFrom = ChangeFrom.map (String); var re = new RegExp(ChangeFrom.join('|'), 'g'); return text.replace(re, function (match) {return ChangeTo[ChangeFrom.indexOf(match)];}); } The key thing is ChangeFrom.map (String) which is necessary because although the ...


0

You may use additional column to filter categories in place. Here's the formula to check if any text matches "golf" or "ski" : =REGEXMATCH(A2,"sky|golf") It will give True if cell A2 contains any word "golf" or "ski". If you want to check more then two words, the easy way is to write them into separate list, say K1:K3: golf sky badminton and then use ...


1

Both parameters of importrange are strings. Strings can include values from other cells, by way of concatenation. For example, =importrange("Spreadsheet A Key", A1 & "!B1") means that the name of the sheet to be imported will be taken from cell A1 of the current sheet. The character & means concatenation: "Names" & "!B1" is "Names!B1".


1

You should move your 3 unique sport values to 3 additional columns: Ski Badminton Golf where you could filter any sport with values: true/false select Cell formula for the additional columns: =IF(IFERROR(FIND(E$3,$B4)),TRUE(),FALSE()) my example UPD: Second solution - Additional one column with unique left-column value and filter by search I've ...


0

How about using notes? I had newline characters in my text that I wanted to show somehow in a single row, too - what I ended up doing was adding the long text into a note. These can be many lines and show when you float - which is about the functionality I was looking for. The only remaining problem really is that the note box is very narrow! Here's what ...


0

This function can be used in each of the destination sheets to copy back to the main sheet. function onEdit(event){ var range = event.range; if(range.getSheet().getName() == "Sheet1" && range.getColumn() > 1 && range.getRow() > 1 ){//example conditionals to limit the action to exclude headers or other static data could be changed ...


0

Google Sheets doesn't have a range syntax for whole sheet range. The following formula will return the range address of the whole sheet named Sheet1 ="Sheet1!"&ADDRESS(1,1,,TRUE)&":"&ADDRESS(ROWS(Sheet1!A:A),COLUMNS(Sheet1!1:1),,TRUE) In order to use it as reference, put it inside of INDIRECT. The following formula will return an array of all ...


2

To replace a fixed string like CIN with the content of a cell, use concatenation &: =QUERY('F/O OFF'!$1:$1000, "select H, J where B = '"&A2&"' label H'', J''") As written, this would work for the second row; you can extend the formula to other rows in the usual way, and the relative reference will be adjusted.


1

To prevent entered text from being converted to a number, precede it by a single quote: for example, entering '1.10 will have the content of the cell displayed as 1.10. Importantly, the quote itself is not shown in the sheet, and is not a part of cell's content. This solves the formatting issue but not sorting: since strings are sorted alphabetically, 1.10 ...


5

While there are answers to the sorting problem such as: How can I sort in Google Sheets by cells containing section (or version) numbers? I've found the simplest method is to keep each significant number in its own column and concatenate them into the final number. Then you can sort on the source columns.


1

You can by using the ImportRange() function, and then reference the first cell. You will just need to reference every sheet that you need the information from. Example: Column A Column B Column C unique Spreadsheet key A1 =importRange(A1,b1) unique Spreadsheet key A1 =importRange(A1,b1) You will also need ...


1

First, you need a lookup table for the letter->digits substitutions. +----+-----+ | A | 00 | | B | 01 | | C | 02 | +----+-----+ (To enter strings like "00", use ="00"). Suppose this table is in cells E1:F18. Then given a string like K1J05359 in cell G1, you can convert it with =vlookup(mid(G1, 3, 1), E1:F18, 2, 0) & left(G1, 2) & right(G1, ...


3

The regular expression should be ^[a-zA-Z0-9,./()]*$, where $ at the end means the end of the string. This forces the entire contents to consist of the specified characters. Also, you don't normally pass a range into data validation formulas: it should be the reference to the upper left corner of the range. For example, if your range is A2:E20, the formula ...


1

I used two arrayformulas for this purpose. One, in cell C2, to create the list of dates: =arrayformula(if(row(C2:C)+A2-2 > max(A2:A), , row(C2:C)+A2-2)) It adds the row number minus 2 to the date in A2. If the result is greater than the last date in column A, this date is out of range and is not shown. Then in column D, vlookup is used to locate the ...


0

You should use the getEvents(startTime, endTime) method. That method returns an array of events. Unfortunately, the documentation kind of hints at what data type is returned, but doesn't "spell out" that the return type is an array. An array has a length property, which you can easily use: theArray.length You can check the length with a conditional ...


0

There appears to be a menu item that can help with this, at: Data → Split text to columns... Single click on a cell and paste your data. It will appear over multiple rows but only one column. Leaving the newly populated cells highlighted, go to the menu Data → Split text to columns... If the app managed to auto-detect your separators, then ...



Top 50 recent answers are included