时间戳

首页 > 解决方案 > 如何使用 API 将外部数据源链接到 Google 表格

google-apps-script - 如何使用 API 将外部数据源链接到 Google 表格

问题描述

我正在尝试将在线数据集链接到 Google 表格，以便 Google 表格自动从特定网站提取数据。

这是我试图从中提取数据的工作表示例之一：https ://www.opendata.nhs.scot/dataset/covid-19-in-scotland/resource/7fad90e5-6f19-455b-bc07-694a22f8d5dc

我对如何处理它给我的代码感到困惑。我希望最后一天的最新数据出现在我的 Google 表格中 - 有什么办法吗？

感谢您提供的任何帮助。

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-api

解决方案

看起来该网站提供了一个直接 URL，然后是日期。

https://www.opendata.nhs.scot/dataset/b318bddf-a4dc-4262-971f-0ba329e09b87/resource/7fad90e5-6f19-455b-bc07-694a22f8d5dc/download/total_cases_by_hb_20210401.csv

因此，为新日期构造该 URL 应该相当简单，然后在一天中的某个时间使用触发器触发该函数并将数据下拉到您选择的工作表中：

function importCSVFromWeb() {
  // Provide the full URL of the CSV file.
  var csvUrl = "https://www.opendata.nhs.scot/dataset/b318bddf-a4dc-4262-971f-0ba329e09b87/resource/7fad90e5-6f19-455b-bc07-694a22f8d5dc/download/total_cases_by_hb_20210401.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

您可以更改上述内容以写入特定工作表而不是活动工作表，您可以将工作表名称存储在“设置”选项卡中的单元格中并作为参数传递

var ss = SpreadsheetApp.getActiveSpreadsheet();

function importCSVFromWeb() {
  // Provide the full URL of the CSV file.
  var csvUrl = "https://www.opendata.nhs.scot/dataset/b318bddf-a4dc-4262-971f-0ba329e09b87/resource/7fad90e5-6f19-455b-bc07-694a22f8d5dc/download/total_cases_by_hb_20210401.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  writeDataToSheet(csvData, "SheetName");
}

// Writes a 2D array of data to sheet
function writeDataToSheet(data, sheetName) {
  goToSheet(sheetName);
  var sheet = ss.getActiveSheet();
  // Leave header row, assume no more than 26 columns and use "Z" for last column
  var range = sheet.getRange("A1:Z");
  range.clear();
  // write date to active sheet starting at the 1st row
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  return sheet.getName();
}

function goToSheet(sheetName) {
    ss.setActiveSheet(ss.getSheetByName(sheetName));
}

在我的自动电子表格中，我总是有一个名为“设置”的选项卡，而不是使用 a1Notation 引用单元格，我更喜欢使用名称范围。

这是一个如何从已命名的单元格中检索值的示例，TargetSheet 是特定单元格的名称：

var sheetName = ss.getRange("TargetSheet").getValue();

然后您可以使用上述函数中的值，而不是对工作表进行硬编码。

function importCSVFromWeb() {
  // Provide the full URL of the CSV file.
  var csvUrl = "https://www.opendata.nhs.scot/dataset/b318bddf-a4dc-4262-971f-0ba329e09b87/resource/7fad90e5-6f19-455b-bc07-694a22f8d5dc/download/total_cases_by_hb_20210401.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheetName = ss.getRange("TargetSheet").getValue();
  writeDataToSheet(csvData, sheetName);
}

推荐阅读