在Excel公式中进行Google搜索，然后将结果放入单元格中 [英] Do a Google search from an Excel formula and put the result in a cell

问题描述

之前

之后(添加地址)

我有一个包含场所名称的电子表格，我想使用Google表格中的公式在下一个单元格中添加这些场所的地址.我想使用公式对结果进行Google搜索，因为我也想抓取许多其他值，并根据需求的变化快速更改手机上的工作表.因此，我想在不使用任何外部编码语言的情况下做到这一点，如果可以避免的话，甚至不用google apps脚本.

Google搜索数据

我在这里找到了一些有关如何使用excel公式执行类似操作的信息:

将Google表格用作基本的网页抓取工具

该网站显示了如何用google搜索具有excel公式的文章的作者姓名，格式如下:

=INDEX(IMPORTXML(A1,"//span[@itemprop='name']"),1)

您能帮我修改一下，以将google搜索的结果返回到Excel单元格吗?

解决方案

具有XPath知识的人可能会删除索引函数，但是以下内容似乎可以可靠地工作.将以下内容粘贴到样本表的B2中:

=index(IMPORTXML("http://www.google.com/search?&q="&"Address+of+"&A2&"+in+british+columbia"&"&oq="&"Address+of+"&A2&"+in+british+columbia","//span"), 15, 1)

这是基于所有场馆均位于不列颠哥伦比亚省的假设.您可以参数化Google搜索部分以添加更多可能的搜索位置.

Before

After (Addresses Added)

I have a spreadsheet with names of venues and I would like to add the addresses of those venues in the next cell using a formula in Google Sheets. I would like to do this using a formula to do a Google Search for the results because I want to scrape many other values as well and quickly change the sheet on my phone as my needs change. Thus I would like to do this without using any outside coding language and not even google apps scripts if I can avoid it.

Google Search for data

I found some information on how to do something similar with an excel formula here:

Using Google Sheets as a basic web scraper

That site shows how to do a google search for an author name of an article with an excel formula with a format like this:

=INDEX(IMPORTXML(A1,"//span[@itemprop='name']"),1)

Can you help me modify this to return the result of a google search into an excel cell?

解决方案

The index function could probably be eliminated by someone with more knowledge of XPath, but nonetheless the below seems to work reliably. The following would be pasted into B2 of your sample sheet:

=index(IMPORTXML("http://www.google.com/search?&q="&"Address+of+"&A2&"+in+british+columbia"&"&oq="&"Address+of+"&A2&"+in+british+columbia","//span"), 15, 1)

This is based upon the assumption that all of the venues are located in British Colombia. You could parameterize the Google search section to add a wider array of possible search locations.

这篇关于在Excel公式中进行Google搜索，然后将结果放入单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！