Google文档格式设置，可将文本转换为工作表 [英] Google docs formatting, converting texts into sheets

问题描述

我已经成功地将Google文档文件发送到Google表格中，并且电子表格设法正确地填充了自己(它将文本格式化为表格，非常简洁)

I've had success in sending a google docs file into a google sheets and the spreadsheet managed to fill itself in correctly (it formats the text into a table, very neat)

下面是我如何设置文本格式的示例:

Below is an example of how I'm formatting my text:

ID, NAME, MOBILE, CITY, COUNTRY, BIRTHDAY, 
3, NameGoesHere1, 21 98658 5548, abcity, countryNameHere, 1998-05-02, 
6, SomeoneElse Joined Here, 21 98535 1218, whereland, Far far away, 1989-11-15, 
5, AnotherCustomer, 21 85482 5245, somecity, Somewhereland, 1999-08-04, 

ID, PRICE, STOCK, ASDF, BASDF, CASDF,

ID, NAME, PRICE, DESCRIPTION, 
2, pen, 1.5, The pen is mightier than the sword, 
3, pencil, 1.0, Can be used to write, 
4, RPG, 150.0, well that escalated quickly, huh, 

EMPTY, 
names, 
goofs, 

ID, FLAVOR, 

(请注意，有两个空表，其中一个以"ID，PRICE，STOCK，ASDF，BASDF，CASDF"和"ID，FLAVOR"为列，故意将它们留为空白)

(Note that there's two empty tables, the one with "ID, PRICE, STOCK, ASDF, BASDF, CASDF" and "ID, FLAVOR" as their columns, they've been left blank on purpose)

通过填充一个单元格:

=IMPORTDATA("<<< <<<google drive url containing text goes here, just copy-paste>>> >>>")

我能够创建多个表，并且所选单元格位于第一个表的左上角，该命令会自动将它们全部调整为Google表格格式.每个表都放在前一个表的下方，从列名开始.

I am able to create a several tables with that chosen cell being the top-left of the first table, and that command fits them all into the google sheets format automatically. Each table being placed below the previous one, starting by the column names.

我正在寻找一种在不同的Google工作表页面中获取每个表格的方法，如下所示:

I'm looking for a way to get each table in a different google-sheet page, like this:

第1页:

ID, NAME, MOBILE, CITY, COUNTRY, BIRTHDAY, 
3, NameGoesHere1, 21 98658 5548, abcity, countryNameHere, 1998-05-02, 
6, SomeoneElse Joined Here, 21 98535 1218, whereland, Far far away, 1989-11-15, 
5, AnotherCustomer, 21 85482 5245, somecity, Somewhereland, 1999-08-04, 

第2页:

ID, PRICE, STOCK, ASDF, BASDF, CASDF,

第3页:

ID, NAME, PRICE, DESCRIPTION, 
2, pen, 1.5, The pen is mightier than the sword, 
3, pencil, 1.0, Can be used to write, 
4, RPG, 150.0, well that escalated quickly, huh, 

以此类推.

我想知道是否有一种方法可以将文本分成不同的页面，如上所示，或者还有另一种方法可以将文件类型从文本转换为工作表，以便可以将其拆分为不同的页面

I would like to know if there's a way to separate text into different pages like shown above, or what's an alternate way to convert the filetype from text to sheets such that I could split it up into different pages

ps .:我需要代码在python中，因为我正在使用pydrive进行文件上传

编辑.:由@Tanaike解决. 重要的调试，以寻找@Tanaike的解决方案:

edit.: SOLVED by @Tanaike. Important debugging to look out for @Tanaike's solution:

• SCOPE必须设置为列表格式，每个url都位于不同的列表索引中
• 确保您的.txt格式为每个页面两个 \ n，下一行为单个\ n.
• 如果出于任何原因您拥有无效的凭据，请在开始出现恐慌之前重新检查"credentials.json"或"client_secrets.json".

• SCOPE must be set with a list format, each url in a different list index
• make sure your .txt is formatted with two \n's per PAGE and single \n for next rows.
• if for whatever reason you have invalid credentials, recheck your "credentials.json" or "client_secrets.json" before starting to panic.

我要再次感谢@Tanaike ，感谢他对我的解决方案的耐心和奉献以及对调试的帮助.

I want to thank @Tanaike yet again, for his patience and dedication to my solution and for his help with debugging it.

推荐答案

• 您有一个如下所示的文本文件.此文本文件已放入您的Google云端硬盘中.您知道文本文件的文件ID.

  • You have a text file like below. This text file is put in your Google Drive. You know the file ID of the text file.

    ID, NAME, MOBILE, CITY, COUNTRY, BIRTHDAY, 
    3, NameGoesHere1, 21 98658 5548, abcity, countryNameHere, 1998-05-02, 
    6, SomeoneElse Joined Here, 21 98535 1218, whereland, Far far away, 1989-11-15, 
    5, AnotherCustomer, 21 85482 5245, somecity, Somewhereland, 1999-08-04, 
    
    ID, PRICE, STOCK, ASDF, BASDF, CASDF,
    
    ID, NAME, PRICE, DESCRIPTION, 
    2, pen, 1.5, The pen is mightier than the sword, 
    3, pencil, 1.0, Can be used to write, 
    4, RPG, 150.0, well that escalated quickly, huh, 
    
    EMPTY, 
    names, 
    goofs, 
    
    ID, FLAVOR, 
    

  • 您想要通过将文本数据分隔为每个空行来将值放置到单独的工作表中.

  • You want to put the values to the individual sheet by separating the text data every empty row.

    • 标签1

    • Tab 1

    ID, NAME, MOBILE, CITY, COUNTRY, BIRTHDAY, 
    3, NameGoesHere1, 21 98658 5548, abcity, countryNameHere, 1998-05-02, 
    6, SomeoneElse Joined Here, 21 98535 1218, whereland, Far far away, 1989-11-15, 
    5, AnotherCustomer, 21 85482 5245, somecity, Somewhereland, 1999-08-04, 
    

  • 标签2

  • Tab 2

    ID, PRICE, STOCK, ASDF, BASDF, CASDF,
    

  • ...

  • 标签5

  • ...

  • Tab 5

    ID, FLAVOR, 
    

  如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

  If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

  我认为仅通过电子表格的内置功能很难实现或可能无法实现您的目标.因此，在这个答案中，我想提出使用Google Apps脚本实现您的目标.

  I thought that your goal is difficult to be achieved or might not be able to be achieved by only the built-in functions of Spreadsheet. So in this answer, I would like to propose to achieve your goal using Google Apps Script.

  此示例脚本的流程如下.

  The flow of this sample script is as follows.

  1. 从文本文件中检索文本数据.
  2. 解析文本数据.
     • 当我看到您的文本数据时，我认为该数据可以解析为CSV数据.
     • 在这里，文本数据被分离以放置到每张纸上.
  1. Retrieve the text data from the text file.
  2. Parse the text data.
     • When I saw your text data, I thought that the data can be parsed as the CSV data.
     • Here, the text data is separated for putting to each sheet.
  • 在这里，每个数据都放在单独的工作表中.

  用法:

  1. 将以下示例脚本复制并粘贴到Google Apps脚本的脚本编辑器中.
     • 那时，请将文本文件的文件ID设置为fileId.
  1. Copy and paste the following sample script to the script editor of Google Apps Script.
     • At that time, please set the file ID of the text file to fileId.
  • 运行脚本时，将打开授权屏幕.因此，请授权范围.

  通过这种方式，脚本可以正常工作.

  By this, the script works.

  function myFunction() {
    var fileId = "###"; // Please set the file ID of text file.
    var newSpreadsheetName = "sampleSpreadsheet"; // Please set new Spreadsheet name.
  
    var data = DriveApp.getFileById(fileId).getBlob().getDataAsString();
    var temp = [];
    var parsedData = Utilities.parseCsv(data).reduce(function(ar, e, i, d) {
      if (e.join("")) {
        temp.push(e);
      } else {
        ar.push(temp);
        temp = [];
      }
      if (i == d.length - 1) ar.push(temp);
      return ar;
    }, []);
    var ss = SpreadsheetApp.create(newSpreadsheetName);
    parsedData.forEach(function(e, i) {
      var sheet = i == 0 ? ss.getSheets()[0] : ss.insertSheet();
      sheet.getRange(1, 1, e.length, e[0].length).setValues(e);
    })
  }
  

  注意:

  • 在此示例脚本中，将创建新的电子表格，并将数据放入每个工作表中.当然，可以将数据放入现有的电子表格中.在这种情况下，需要修改脚本.
  • Class DriveApp
  • SpreadsheetApp类
  • reduce()
  • parseCsv(csv)
  • Class DriveApp
  • Class SpreadsheetApp
  • reduce()
  • parseCsv(csv)

  如果我误解了您的问题，而这不是您想要的方向，我深表歉意.

  If I misunderstood your question and this was not the direction you want, I apologize.

  • 您要使用带有python的google-api-python-client来实现上述目标.
  • 您已经能够通过python使用Drive API和Sheets API.

  我可以像上面那样理解.在这种情况下，将使用Drive API和Sheets API.云端硬盘API从Google云端硬盘上的文本文件中检索数据. Sheets API使用这些值创建新的电子表格.在这种情况下，为了使用Sheets API，我使用了google-api-python-client.通过这种方式，我还将Drive API与google-api-python-client一起使用.

  I could understand like above. In this case, Drive API and Sheets API are used. Drive API retrieves the data from the text file on Google Drive. Sheets API creates new Spreadsheet using the values. In this case, in order to use Sheets API, I used google-api-python-client. By this, I also used Drive API with google-api-python-client.

  fileId = '###'  # Please set the file ID of text file.
  
  sheets = build('sheets', 'v4', credentials=creds)
  drive = build('drive', 'v3', credentials=creds)
  
  # Retrieve data from Google Drive and parse data as an array.
  data = drive.files().get_media(fileId=fileId).execute()
  csvData = [row.split(",") for row in str(data, 'utf-8').split("\n")]
  ar = []
  temp = []
  for i, row in enumerate(csvData):
      if "".join(row) != "":
          row = [v.strip() for v in row]
          temp.append(row)
      else:
          ar.append(temp)
          temp = []
      if i == len(csvData) - 1:
          ar.append(temp)
  
  sheetsObj = []
  valuesUpdateReq = []
  for i, sheet in enumerate(ar):
      if bool(sheet):
          sheetName = "Sheet" + str(i + 1)
          sheetsObj.append({"properties": {"title": sheetName}})
          valuesUpdateReq.append({"values": sheet, "range": sheetName, "majorDimension": "ROWS"})
  
  # Request to Sheets API.
  body = {"properties": {"title": "sampleSpreadsheet"}, "sheets": sheetsObj}
  res1 = sheets.spreadsheets().create(body=body).execute()
  batch_update_values_request_body = {"data": valuesUpdateReq, "valueInputOption": "USER_ENTERED"}
  res2 = sheets.spreadsheets().values().batchUpdate(spreadsheetId=res1["spreadsheetId"], body=batch_update_values_request_body).execute()
  print(res2)
  

  • 运行脚本时，将从Google云端硬盘检索数据并创建新的电子表格.在这种情况下，这些值将使用"USER_ENTERED"放入电子表格.这样，可以将值解析为字符串，数字和日期.而且，不使用字符顶部的单引号.
  • 关于credentials=creds，请检查 Python快速入门.
  • When you run the script, the data is retrieved from Google Drive and the new Spreadsheet is created. In this case, the values are put to the Spreadsheet with "USER_ENTERED". By this, the values can be parsed as the string, number and date. And also, the single quote of the top of character is not used.
  • About credentials=creds, please check the Quickstart for python.

  这篇关于Google文档格式设置，可将文本转换为工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！