使用谷歌查询下载已发布工作表的部分内容 [英] Using google query to download parts of a published sheet

问题描述

这是有效的：

  curl'https://docs.google.com/spreadsheets/d/e/2PACX- 1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc / pub？gid = 911257845& single = true& output = csv'
  

然而，我只想选择count> 300的行。

编码之前的查询是

  select * where F> 300 
  

编码后

  select％20 *％20where％20F％3E300 
  

/ p>

  https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/pub?gid = 911257845& output = csv& tq = select％20 *％20 where％20F％3E300 
  

上面的作品检索一个文件，但它返回的是整个文件，并且不会过滤。

请注意，已发布的Web表单的格式为

https：// docs。 google.com/spreadsheets/d/e/KEY/pub?gid=GID

https://docs.google.com/spreadsheets/d/e/2PACX-1vS 3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc / pub？gid = 911257845

这是有效的。将& output = csv添加到它（在&之前没有空格），并将其作为csv文件下载。这在Excel中打开，并显示表中的数据。

我试过这个：
$ b $ HTTPS： //docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/pub?gid=911257845&output=csv&tq=select%20 *％20where％20F％3E％20300

和

https://docs.google.com/spreadsheets/d/e / 2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc / gviz / tq？gid = 911257845& output = csv& tq = select％20 *％20其中％20F％3E300

并获取错误 - 资源不可用。

上面的页面应该公开给那些想要尝试的人。

这可能是发布工作表和共享整个电子表格给任何有链接的人之间的问题。

我创建了一个新页面它使用importrange（），从主表中汲取页面，并且该页面是公开的。

https://docs.google.com/spreadsheets/d/1-lqLuYJyHAKix-T8NR8wV8ZUUbVOJrZTysccid2-ycs/edit？ usp =共享

解决方案

如何修改？

< h3>修改点：

• 使用查询时，请使用 https://docs.google.com / spreadsheets / d / ### file ID ### / gviz / tq？gid = ###& tq = ### query ### 。


• 当选择％20 *％20 where％20％F％3E300 被解码，它是 select *其中％F> 300 。
  
  
  
  
  • select * where F> 300 select％20％2a％20 where％20F％20％3e％20300 。
  
  


• 为了输出CSV，请使用 tqx = out：csv 。


• 共享电子表格。
  
  
  
  
  • 在Google云端硬盘
    
    
    
    
    • 在电子表格文件中
      
    • 右键点击 - >共享 - >高级 - >点击隐私 - 只有您可以访问的变化
    
    
    • 勾选任何有链接的人
    >
    
    • 点击保存
    
    
    • 在分享链接中复制网址。
      
      
      
      
      • 从检索文件ID> https://docs.google.com/spreadsheets/d/###文件ID ### / edit？usp = sharing
      
      
    
    
  
  

修改卷曲命令：

  curl'https://docs.google.com/spreadsheets/d/### file ID ### / gviz / tq？gid = 911257845& tq = select％20％2a％20 where％20F％20％3e％20300& amp ; tqx = out：csv'
  

参考：

• 查询语言参考

如果我误解了您的问题，我很抱歉。

编辑： h2>

以下2个网址是您的网址与我的答案之间的比较。我的答案的网址与您的网址相匹配。

1。您的网址

  https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/ gviz / tq？gid = 911257845& output = csv& tq = select％20 *％20 where％20F％3E300 
  

当上面的URL分开时，

1. https://docs.google.com/spreadsheets/d / e /
   
   
   
   
   • e / 不是必需的。
   
   


2. 2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc
   
   
   
   
   • 这不是电子表格的文件ID。
     


3. / gviz / tq


4. gid = 911257845


5. output = csv


6. tq = select％20 *％20 where％20F％3E300





2。在我的答案中与您的网址匹配

  https://docs.google.com/spreadsheets/d/###文件ID ### / gviz / tq？gid = ###& tqx = out：csv& tq = ###查询### 
  

当上面的URL分开时，

1. https://docs.google .com / spreadsheets / d /


2. ###文件ID ###
   
   
   
   
   • 您可以在 here 。
   
   


3. / gviz / tq
4. gid = ###


   
   
   • 您可以使用 gid = 911257845 。
     
   
   
   • tqx = out：csv
     
     
     
     
     • 必须使用 output = csv
     
     
     • tq = ###查询###
       
       
       
       
       • 您可以使用 tq = select％20 *％20 where％20F％3E300 。
       
       
       
       

       注意：

       
       
       
       
       
       • strong>每个数字都对应。
       
       
       • 请按照以下方式分享电子表格。这与Spreadsheet上的发布到网络不同。
         
         
         
         
         • 在Google云端硬盘
           
           
           
           
           • 在电子表格文件中
           
           
           • 右键单击 - >共享 - >高级 - >点击私人 - 只有您可以访问 b $ b
           • 勾选关于任何有链接的人
           
           
           • 点击保存
           
           
           • 在分享链接
             
             
             
             
             • 从 https://docs.google.com/spreadsheets/d/###
             
             
           
           
         
         
       
       
       
       

       This works:

       curl 'https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/pub?gid=911257845&single=true&output=csv'
       

       however I want to only pick up rows where count > 300.

       The query before encoding would be

       select * where F > 300
       

       After encoding

       select%20*%20where%20F%3E300
       

       So the url becomes

       https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/pub?gid=911257845&output=csv&tq=select%20*%20where%20F%3E300
       

       The line above works retrieves a file, but it returns the whole file, and doesn't filter.

       Note that a published web sheet has the form

       https://docs.google.com/spreadsheets/d/e/KEY/pub?gid=GID

       https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/pub?gid=911257845

       This works. Adding &output=csv to it (no space before the &) works, and it downloads as a csv file. This opens in excel and shows the data in the table.

       I tried this:

       https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/pub?gid=911257845&output=csv&tq=select%20*%20where%20F%3E%20300

       and

       https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/gviz/tq?gid=911257845&output=csv&tq=select%20*%20where%20F%3E300

       and get errors -- resource not available.

       The page above should be public for people who want to try.

       This may be an issue between publishing a sheet, and sharing a whole spread sheet to anyone who has the link.

       I've created a new page that uses importrange() that slurps up the page from the main sheet, and that one is public.

       https://docs.google.com/spreadsheets/d/1-lqLuYJyHAKix-T8NR8wV8ZUUbVOJrZTysccid2-ycs/edit?usp=sharing

       解决方案

       How about this modification?

       Modification points :

       • When it uses query, please use like https://docs.google.com/spreadsheets/d/### file ID ###/gviz/tq?gid=###&tq=### query ###.
       • When select%20*%20where%20%F%3E300 is decoded, it is select * where %F>300.
         • select * where F > 300 is select%20%2a%20where%20F%20%3e%20300.
       • In order to output CSV, please use tqx=out:csv.
       • Please share the Spreadsheet.
         • On Google Drive
           • On the Spreadsheet file
           • right-click -> Share -> Advanced -> Click "change" at "Private - Only you can access"
           • Check "On Anyone with the link"
           • Click "Save"
           • At "Link to share", copy URL.
             • Retrieve file ID from https://docs.google.com/spreadsheets/d/### file ID ###/edit?usp=sharing

       Modified curl command :

       curl 'https://docs.google.com/spreadsheets/d/### file ID ###/gviz/tq?gid=911257845&tq=select%20%2a%20where%20F%20%3e%20300&tqx=out:csv'
       

       Reference :

       • Query Language Reference

       If I misunderstand your question, I'm sorry.

       Edit :

       The following 2 URLs are the comparison between your URL and my answer. The URL of my answer was matched to your URL.

       1. Your URL

       https://docs.google.com/spreadsheets/d/e/2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc/gviz/tq?gid=911257845&output=csv&tq=select%20*%20where%20F%3E300
       

       When above URL is separated,

       1. https://docs.google.com/spreadsheets/d/e/
          • e/ is not required.
       2. 2PACX-1vS3iBtVf4i_won5zAN9NGPqhcd6CcTb-4QHxpisSjCmlgV95B6mFmZvtMaC9GPvD7m8kD-6XLkVAhfc
          • This is not the file ID of spreadsheet.
       3. /gviz/tq
       4. gid=911257845
       5. output=csv
       6. tq=select%20*%20where%20F%3E300

       2. In my answer matched to your URL

       https://docs.google.com/spreadsheets/d/### file ID ###/gviz/tq?gid=###&tqx=out:csv&tq=### query ###
       

       When above URL is separated,

       1. https://docs.google.com/spreadsheets/d/
       2. ### file ID ###
          • You can see the detail of the file ID of spreadsheet at here.
       3. /gviz/tq
       4. gid=###
          • You can use gid=911257845.
       5. tqx=out:csv
          • This has to be used instead of output=csv.
       6. tq=### query ###
          • You can use tq=select%20*%20where%20F%3E300.

       Note :

       • Each number corresponds.
       • And please share the Spreadsheet as follows. This is difference from "Publish to the web" on Spreadsheet.
         • On Google Drive
           • On the Spreadsheet file
           • right-click -> Share -> Advanced -> Click "change" at "Private - Only you can access"
           • Check "On Anyone with the link"
           • Click "Save"
           • At "Link to share", copy URL.
             • Retrieve file ID from ``https://docs.google.com/spreadsheets/d/###

       这篇关于使用谷歌查询下载已发布工作表的部分内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！