我正在制作一个自定义功能,从网站下载ZIP文件(csv)。可供下载的数据集有多个符号(2200行),12列。1 -目标是获取一个zip文件与自定义的URL(具有日期后缀)和提取数据。2 -运行Filter/query获取所选日期和符号的所选属性值的数据(作为输入)。3 -输出单元格给出选择的值- url,日期,属性(用于选择列)和符号。分享表请检查以便更好地理解。工作表链接为https://docs.google.com/spreadsheets/d/1e2qzU3RwYVILAdwEKNw-kbEICYGcNh5P0PfhOprFcgw/edit?usp=sharing
直到现在使用的脚本在'Temp'表中获取和解压缩文件。需要进一步的帮助和指导才能从这里走得更远。代码如下-"
/**
* Import csv file of a date
*
* @param {URL} input for custom date file name to be downloaded
* @param {symbol} Data for this symbol is to be extracted
* @param {date} Data for this date is to be extracted
* @param {attribute} Data for this attribute is to be extracted
* @ return data array of listed symbols for a range of listed dates of desired attributes
* @ customfunction
*/
function importbhav(url,symbol,date,attribute) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Temp') ; // Temp location for unzipping csv file
var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1') ; // Final location for data reqd. in desired format (i.e. date-wise , symbol-wise of selected attributes )
var Cell_A = s1.getActiveRange().getValue(); // URL reference which is date linked
var Cell_B = s1.getActiveRange('$B$5:$B').getValue(); // symbol
var Cell_C = s1.getActiveRange('$D$4:$KO$4').getValue(); // date
var Cell_D = s1.getActiveRange('G1').getValue(); // date
var url = Cell_A ;
var symbol = Cell_B ;
var date = Cell_C ;
var Attribute = Cell_D
// attribute list
var Attribute = [Dattributes_1,Dattributes_2,Dattributes_3,Dattributes_4,Dattributes_5,Dattributes_6,Dattributes_7,Dattributes_8,Dattributes_8,Dattributes_9] ;
var Dattributes_1="SYMBOL" ;
var Dattributes_2="SERIES" ;
var Dattributes_3="OPEN" ;
var Dattributes_4="HIGH" ;
var Dattributes_5="LOW" ;
var Dattributes_6="CLOSE" ;
var Dattributes_7="LAST" ;
var Dattributes_8="TOTTRDVAL" ;
var Dattributes_9="TOTALTRADES" ;
// downloading + unzipping file
var zipblob = UrlFetchApp.fetch(url).getBlob();
var unzipblob = Utilities.unzip(zipblob);
var unzipstr=unzipblob[0].getDataAsString();
var csv = Utilities.parseCsv(unzipstr);
ss.getRange(1, 1, csv.length, csv[0].length).clearContent().setValues(csv);
// return input
}
;"
描述
我相信您所要求的是,如果您将公式放在任何单元格中,它将查找该列到第5行查找日期,并沿着B列的行查找符号。这里有一个例子。我把url csv的搜索留给你。
您只需在任何单元格中键入=test()
,结果就会显示在该单元格中。
Code.gs
function test() {
try {
let cell = SpreadsheetApp.getActiveRange();
let sheet = SpreadsheetApp.getActiveSheet();
let date = sheet.getRange(4,cell.getColumn()).getValue(); // date
let symbol = sheet.getRange(cell.getRow(),2).getValue(); // symbol
let option = sheet.getRange(1,7).getValue(); // option
return date.toString()+","+symbol+","+option;
}
catch(err) {
Logger.log(err);
}
}
参考
- SpreadsheetApp.getActiveRange ()