将数据从Google云端硬盘中的CSV文件导入到Google表格中 [英] Import data from CSV file in Google Drive, to Google Sheet

问题描述

我正在使用SAS每24小时生成两个CSV文件.而且我已经使用蝙蝠脚本将生成的CSV文件保存在Google云端硬盘的文件夹中. CSV文件已替换，因此文件夹中始终只有这两个文件.

I am using SAS to generate two CSV files every 24 hours. And I have used a bat script to save the generated CSV files in a folder in Google Drive. The CSV files are replaced, so there will always only be these two files in the folder.

CSV文件用，"分隔，并且仅包含三列或四列.

The CSV files are seperated by "," and contain only three columns or four columns.

我想在Google工作表和CSV文件之间创建直接链接，以便Google工作表自动更新为最新的数字.

I want to create a direct link between a google sheet and the CSV files, so that the google sheet updates to the newest numbers automatically.

我尝试使用函数"ImportData"但没有运气

I have tried using the function "ImportData" with no luck

=IMPORTDATA("https://drive.google.com/file/d/123231jshu231731/edit?usp=sharing")

其中123231jshu231731是file_id.

where 123231jshu231731 is the file_id.

但是我得到的错误是

Result was not automatically expanded, please insert more columns (896).

这没有意义，因为文件只有3列

Which does not make sense as the file only have 3 columns

希望有人能更好地解决我的自动化问题

Hope some have a better solution to my automatization problem

谢谢

推荐答案

将csv文件发布到网络上

显然，IMPORTDATA仅适用于发布到Web上的文档.您可以通过两种方式使它工作:

Apparently IMPORTDATA only works for documents published to the web. Two ways you could get it to work for you would be to:

1. 将您的csv文件存储在公共的Google驱动器文件夹中
2. 以其他方式将您的csv文件托管在网络上-我想您需要使用csv文件在计算机上运行服务器.

但是，这涉及将您的csv数据公开给连接到Internet的任何人(可能是您不想要的东西)

However, this involves making your csv data public to anyone connected to the internet (probably something you don't want)

将csv文件设为私有

如果您希望数据保持私密性，则可以制作一个脚本，以定期将csv数据加载到电子表格中.

If you want your data to remain private, you could make a script that periodically loads your csv data into the spreadsheet.

Google提供了一个服务调用 Google Apps脚本(GAS)，用JavaScript编写脚本以在Google驱动器中执行更复杂的任务.

Google provides a service call Google Apps Script (GAS) which would allow you to write scripts in JavaScript to preform more complex tasks in google drive.

在GAS内，您可以创建称为可安装触发器的内容.您可以将其设置为在指定的时间间隔定期运行.

Within GAS, you can create something called an installable trigger which you can set to run periodically on a set time interval.

这是我编写脚本以上传csv数据的基本布局:

Here's a basic layout of how I'd go about writing the script to upload your csv data:

function timedTrigger() {
  // list the csv file ids
  var baseballScoresFileId = "123231jshu231731";
  var donutPricesFileId = "984732ageyn555646";

  // get the spreadsheet by it's id
  var ss = SpreadsheetApp.openById("the spreadsheet id");

  // get the sheets you want to print the data to
  var baseballSheet = ss.getSheetByName("Baseball Scores");
  var donutsSheet = ss.getSheetByName("Donuts Scores");

  // print the data to the first row first columnscript
  printCsvData(baseballScoresFileId, baseballSheet, 1, 1);

  printCsvData(donutPricesFileId, donutsSheet, 5, 2); // prints to B5
}

// This function loads the data from a csv fileId
//   and prints it to the sheet starting at the cell
//   located at (row, col)
// It works just like IMPORTDATA except you specify the row and col in the 
function printCsvData(fileId, sheet, row, col) {
  // get data from file
  var data = DriveApp.getFileById(fileId).getBlob().getDataAsString();

  // parse the csv string into two dimensional array
  var values = parseCsvData(data);

  // print the values into the range starting at row, col
  sheet.getRange(row, col, values.length, values[0].length).setValues(values);
}

// This function converts a string of comma
//   separated values and converts them into
//   a two dimensional array
function parseCsv(string) {
  var values = [];
  // ...
  return values;
}

我不太确定您如何格式化CSV文件；我最好的猜测是:

I'm not quite sure how you formatted your CSV files; my best guess would be:

function parseCSV(string) {
  return string.split("\n").map(function (row) {
    return row.split(",");
  });
}

您可能需要担心多余的空格.

You may need to worry about extra whitespace.

如果您不熟悉JavaScript或编程，那么这将是一个学习的好机会.但是，如果迫切需要加载csv文件，请注意，学习如何设置脚本可能需要5到20个小时.无论哪种方式，祝你好运！

If you're not familiar with JavaScript or programming, this would be a great opportunity to learn. However, if loading the csv files isn't a dire need, just note that it may take you 5-20 hours to learn how to set up the script. Either way, good luck!

这篇关于将数据从Google云端硬盘中的CSV文件导入到Google表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！