google sheets – DGET fails to look properly when two entries are similar

From the Google official support site

DGET is the only database function that does not aggregate – it will return an error if the filtered data has either no matching elements or more than one matching element.

So yes. This is the expected behaviour.

As your best alternative would be to use the QUERY function

=QUERY(M1:N,"select N where M='Daedalus I' ",1)

OR

=QUERY(M11:N16,"select N where M matches 'Lucky M' ",1)

enter image description here

Of course you will argue
“But Daedalus I is not the same as Daedalus II
Correct.

What happens with DGET?
You should think of DGET as an alternative of the where QUERY clause.
The sting comparison operators would be matches, contains, like, starts with, ends with.

DGET is an alternative for the starts with in a QUERY
But DGET does NOT aggregate.
Thus the error.


EDIT (after taking a look at your sheet)

You can also use

=QUERY(A1:B,"select B where A matches '"&D2&"' ",0)

enter image description here

google sheets – DGET fails to look properly when two entries are similar

From the Google official support site

DGET is the only database function that does not aggregate – it will return an error if the filtered data has either no matching elements or more than one matching element.

So yes. This is the expected behaviour.

As your best alternative would be to use the QUERY function

=QUERY(M1:N,"select N where M='Daedalus I' ",1)

OR

=QUERY(M11:N16,"select N where M matches 'Lucky M' ",1)

enter image description here

Of course you will argue
“But Daedalus I is not the same as Daedalus II
Correct.

What happens with DGET?
You should think of DGET as an alternative of the where QUERY clause.
The sting comparison operators would be matches, contains, like, starts with, ends with.

DGET is an alternative for the starts with in a QUERY
But DGET does NOT aggregate.
Thus the error.


EDIT (after taking a look at your sheet)

You can also use

=QUERY(A1:B,"select B where A matches '"&D2&"' ",0)

enter image description here

formulas – How to reference a cell outside range using query function in Google Sheets

I’m trying to copy cells B to F to another sheet if the text in cell J contains specific text. As an example case, I’m using the query function as:

=query('Sheet 1'!B1:J,"Select * Where J contains 'Apples'") 

and that works fine, but I don’t want all cells B to J to be copied across, just cells B to F.

Google Sheets formulas that needs to read data from drop down boxes

I am trying to create a master spreadsheet that is pulling information from several other sheets into this one master sheet. Then I planned on using this master sheet to pull statistics. In the sign up sheets there are drop down boxes for Yes, No. My master sheet will pull all data except the Yes, No from the drop down boxes. I can not figure out why!!

facebook – Google Sheets importxml help

I am trying to scrape the number of photos statistic from a Facebook group (currently 432,780) at:

https://m.facebook.com/groups/553511078046085?view=info

I have copied the relevant xpath and used the following formula in Google Sheets:

=importxml(“https://m.facebook.com/groups/553511078046085?view=info”,”//*(@id=’u_0_y_OX’)”)

However, it doesn’t work and keeps showing the error message “imported content is empty”

Any ideas what I’m doing wrong?

Thanks in advance.

google sheets – Work flow using that employees can use to submit expenses for reimbursement and also request supplies

Can anyone recommend a work flow using some kind of web application that my employees can use to submit expenses for reimbursement and also request supplies? We’re not a large company, around 10 people in the field.

I would like to have a queue of expense reimbursement requests and supply requests from my field workers that my admin people can fulfill. Right now people just text me and I send them money or order them stuff. It’s chaos.

I would like something where my field people can submit a request, sometimes with an image or other document (usually a screen shot of a receipt or perhaps a picture of the supply they need), then my admin people can see the list of requests and order supplies or tell me to send monetary reimbursements.

Later on I would like to be able to have some way of seeing how much money and supplies each employee is using/spending. Such as by running a search or report.

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

If the content is added dynamically (by using Javascript), it can’t be imported by using Google Sheets built-in functions. Also if the website webmaster have taken certain measures, this functions will not able to import the data.


To check if the content is added dynamically, using Chrome,

  1. Open the URL of the source data.
  2. Press F12 to open Chrome Developer Tools
  3. Press Control+Shift+P to open the Command Menu.
  4. Start typing javascript, select Disable JavaScript, and then press Enter to run the command. JavaScript is now disabled.

JavaScript will remain disabled in this tab so long as you have DevTools open.

Reload the page to see if the content that you want to import is shown, if it’s shown it could be imported by using Google Sheets built-in functions, otherwise it’s not possible but might be possible by using other means for doing web scraping.

According to Wikipedia,

Web scraping, web harvesting, or web data extraction is data scraping used for extracting data from websites.

The webmasters could use robots.txt file to block access to website. In such case the result will be #N/A Could not fetch url.

The webpage could be designed to return a special a custom message instead of the data.


IMPORTDATA, IMPORTFEED, IMPORTHTML and IMPORTXML are able to get content from resources hosted on websites that are:

  • Publicly available. This means that the resource doesn’t require authorization / to be logged in into any service to access it.
  • The content is “static”. This mean that if you open the resource using the view source code option of modern web browsers it will be displayed as plain text.
    • NOTE: The Chrome’s Inspect tool shows the parsed DOM; in other works the actual structure/content of the web page which could be dynamically modified by JavaScript code or browser extensions/plugins.
  • The content has the appropriated structure.
    • IMPORTDATA works with structured content as csv or tsv doesn’t matter of the file extension of the resource.
    • IMPORTFEED works with marked up content as ATOM/RSS
    • IMPORTHTML works with marked up content as HTML that includes properly markedup list or tables.
    • IMPORTXML works with marked up content as XML or any of its variants like XHTML.
  • Google servers are not blocked by means of robots.txt or the user agent.

On W3C Markup Validator there are several tools to checkout is the resources had been properly marked up.

Regarding CSV check out Are there known services to validate CSV files

It’s worth to note that the spreadsheet

  • should have enough room for the imported content; Google Sheets has a 5 million cell limit by spreadsheet, according to this post a columns limit of 18278, and a 50 thousand characters as cell content even as a value or formula.
  • it doesn’t handle well large in-cell content; the “limit” depends on the user screen size and resolution as now it’s possible to zoom in/out.

References

Related

The following question is about a different result, #N/A Could not fetch url

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

If the content is added dynamically (by using Javascript), it can’t be imported by using Google Sheets built-in functions. Also if the website webmaster have taken certain measures, this functions will not able to import the data.


To check if the content is added dynamically, using Chrome,

  1. Open the URL of the source data.
  2. Press F12 to open Chrome Developer Tools
  3. Press Control+Shift+P to open the Command Menu.
  4. Start typing javascript, select Disable JavaScript, and then press Enter to run the command. JavaScript is now disabled.

JavaScript will remain disabled in this tab so long as you have DevTools open.

Reload the page to see if the content that you want to import is shown, if it’s shown it could be imported by using Google Sheets built-in functions, otherwise it’s not possible but might be possible by using other means for doing web scraping.

According to Wikipedia,

Web scraping, web harvesting, or web data extraction is data scraping used for extracting data from websites.

The webmasters could use robots.txt file to block access to website. In such case the result will be #N/A Could not fetch url.

The webpage could be designed to return a special a custom message instead of the data.


IMPORTDATA, IMPORTFEED, IMPORTHTML and IMPORTXML are able to get content from resources hosted on websites that are:

  • Publicly available. This means that the resource doesn’t require authorization / to be logged in into any service to access it.
  • The content is “static”. This mean that if you open the resource using the view source code option of modern web browsers it will be displayed as plain text.
    • NOTE: The Chrome’s Inspect tool shows the parsed DOM; in other works the actual structure/content of the web page which could be dynamically modified by JavaScript code or browser extensions/plugins.
  • The content has the appropriated structure.
    • IMPORTDATA works with structured content as csv or tsv doesn’t matter of the file extension of the resource.
    • IMPORTFEED works with marked up content as ATOM/RSS
    • IMPORTHTML works with marked up content as HTML that includes properly markedup list or tables.
    • IMPORTXML works with marked up content as XML or any of its variants like XHTML.
  • Google servers are not blocked by means of robots.txt or the user agent.

On W3C Markup Validator there are several tools to checkout is the resources had been properly marked up.

Regarding CSV check out Are there known services to validate CSV files

It’s worth to note that the spreadsheet

  • should have enough room for the imported content; Google Sheets has a 5 million cell limit by spreadsheet, according to this post a columns limit of 18278, and a 50 thousand characters as cell content even as a value or formula.
  • it doesn’t handle well large in-cell content; the “limit” depends on the user screen size and resolution as now it’s possible to zoom in/out.

References

Related

The following question is about a different result, #N/A Could not fetch url

google sheets – Obtain breakdown based on a different column

Is it possible to obtain nested values of a column immediately after the value they are related to from another column?

Basically, I have a value (Order Item Cost coming from a unique Order Item Id), which is always linked to several unique Order Item Option Ids (and their Cost). I would like to be able to have them in the same column as in here as in a sort of break down and (bonus point, but not crucial) potentially adding a row for the total after each unique Order Item Id) as well .

I have added the status of my current dataset and the Solution I would like to get to.

It would be great if this can be done through the Google Spreadsheet itself, but I would also be open to SQL suggestions, as this comes from a SQL Database.

Auto-sorting in google sheets and excel by custom valued sorting

I want my data to be auto sorted by 2 columns every-time I append or change values. Also sorting is not just A to Z or Ascending/Descending but Custom valued sorting as example shown below:

1- High
2- Normal
3- Low

Can anyone help the soonest?