使用基本验证功能将csv抓取到Google表格 [英] Pull csv to Google Sheets with basic authentication

问题描述

我需要从一个需要基本auth的URL拉取csv。

I need to pull a csv from a URL that requires basic auth.

我发现这个代码可以工作，除了解析csv，清除和设置细胞。我认为有一些错误，因为它是旧的代码，如 clear.contents（）应该 clear.content（）。

I have found this code that works, apart from parsing the csv, clearing and setting the cells. I think there is a few bugs as it is old code, such as clear.contents() should be clear.content().

即使有硬编码数据，但仍有人努力让它工作，还有其他人找到了解决方案？ p>

And even with hard coding the data in where sheets is, im still struggling to get it to work, has anyone else found a solution?:

// this function assumes the CSV has no fields with commas,
// and strips out all the double quotes
function parseCsvResponse(csvString) {
    var retArray = [];

    var strLines = csvString.split(/\n/g);
    var strLineLen = strLines.length;
    for (var i = 0; i < strLineLen; i++) {
        var line = strLines[i];
        if (line != '') {
            retArray.push(line.replace(/"/g, "").split(/,/));
        }
    }

    return retArray;
}

function populateSheetWithCSV(sheet, csvUrl, user, pw) {

    // request the CSV!
    var resp = UrlFetchApp.fetch(csvUrl, {
        headers: {
            // use basic auth
            'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)
        }
    });

    // parse the response as a CSV
    var csvContent = parseCsvResponse(resp.getContentText());

    // clear everything in the sheet
    sheet.clearContents().clearFormats();

    // set the values in the sheet (as efficiently as we know how)
    sheet.getRange(1, 1, csvContent.length /* rows */, csvContent[0].length /* columns */).setValues(csvContent);

}

推荐答案

没有看到你的CSV文件，它不是那么容易预测哪里出错了。但是，我发现更改 parseCsvResponse 方法中的正则表达式包含 \r 可以解决我的问题。因此，该行显示

Well, as I haven't seen your CSV file, it's not that easy to predict where things goes wrong. However, I found that changing the regular expression in the parseCsvResponse method to include \r fixed the problem for me. Thus, the line reads

var strLines = csvString.split（/ \\\
| \r / g）;

var strLines = csvString.split(/\n|\r/g);

其次，我看不到你从哪里得到表 -variable。我使用了以下代码：

Secondly, I can't see where you get the sheet-variable from. I used the following line of code:

var sheet = SpreadsheetApp.getActiveSheet（）;

var sheet = SpreadsheetApp.getActiveSheet();

最后，我注意到脚本在我尝试运行它时请求授权。我假设你已经授予所需的权限？

Lastly, I noticed that the script requested authorization when I tried to run it. I assume you have granted the permissions required?

这篇关于使用基本验证功能将csv抓取到Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！