在导入到Google Spreadsheet之前对CVS进行过滤 [英] Filter CVS before importing to Google Spreadsheet
本文介绍了在导入到Google Spreadsheet之前对CVS进行过滤的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在Google电子表格中有一个脚本,该脚本从URL下载压缩的CSV,然后将其导入到电子表格中.实际上,CVS太大了,我不需要它的所有数据.我的问题是,如何在将数据导入电子表格之前对其进行过滤?例如,使用X值过滤列A.
I have a Script in a Google Spreadsheet, the script downloads a zipped CSV from an URL and then import it to the spreadsheet. Actually, the CVS is too big and I don't need all the data from it. My question is, How can I filter the data before importing it to the spreadsheet? For example filter Column A with X value.
这是我到目前为止的代码:
This is the code I have so far:
function descargarzip()
{
var urldescarga = "http://187.191.75.115/gobmx/salud/datos_abiertos/datos_abiertos_covid19.zip"
var url = urldescarga
var zipblob = UrlFetchApp.fetch(url).getBlob();
zipblob.setContentTypeFromExtension();
var unzipblob = Utilities.unzip(zipblob);
var unzipstr=unzipblob[0].getDataAsString();
var csv = Utilities.parseCsv(unzipstr);
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
ss.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}
先谢谢您!
推荐答案
尝试一下:
function descargarzip() {
var urldescarga = "http://187.191.75.115/gobmx/salud/datos_abiertos/datos_abiertos_covid19.zip"
var url = urldescarga
var zipblob = UrlFetchApp.fetch(url).getBlob();
zipblob.setContentTypeFromExtension();
var unzipblob = Utilities.unzip(zipblob);
var unzipstr=unzipblob[0].getDataAsString();
var csv = Utilities.parseCsv(unzipstr);
var x = 'You enter the contents x';
csv.forEach(function(r,i){
if(r[0]==x) {
r[0]='';//You have to put something back in there because the csv has to be a rectangular array for setValues();
}
});//You could remove an entire line or an entire column
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
ss.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}
如果@TheMaster是正确的,请尝试以下操作:
If @TheMaster is correct then try this:
function descargarzip() {
var urldescarga = "http://187.191.75.115/gobmx/salud/datos_abiertos/datos_abiertos_covid19.zip"
var url = urldescarga
var zipblob = UrlFetchApp.fetch(url).getBlob();
zipblob.setContentTypeFromExtension();
var unzipblob = Utilities.unzip(zipblob);
var unzipstr=unzipblob[0].getDataAsString();
var csv = Utilities.parseCsv(unzipstr);
var x = 'You enter the contents x';
var d=0;
//I tested this on some of my data and I believe it works
for(var i=0;(i-d)<csv.length;i++) {
if(csv[i-d][0]==x) {
csv.splice(i-d++,1);//I think this is correct but I could be wrong in here because I mostly use this approach for deleting rows not portions of the array. So if you have problems the please share your csv data and I will debug it.
}
}
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
ss.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}
这篇关于在导入到Google Spreadsheet之前对CVS进行过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文