Google docs formatting, converting texts into sheets

Question

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,

(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)

By filling a cell with:

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

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.

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

page1:

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,

page2:

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

page3:

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,

and so on.

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.: I need the code to be in python, as I'm working with pydrive for the file uploads

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

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.

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

Tanaike · Accepted Answer · 2020-01-29T23:30:38.900

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.

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,

Tab 2
```
ID, PRICE, STOCK, ASDF, BASDF, CASDF,
```
...
Tab 5
```
ID, FLAVOR, 
```

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

Issue and workaround:

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.

Flow:

The flow of this sample script is as follows.

Retrieve the text data from the text file.
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.
Create new Spreadsheet.
Put the parsed data to the new Spreadsheet.
- Here, each data is put to the individual sheet.

Usage:

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.
Run the function of myFunction().
- When the script is run, the authorization screen is opened. So please authorize the scopes.

By this, the script works.

Sample script:

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);
  })
}

Note:

In this sample script, new Spreadsheet is created abd the data is put to each sheet. Of course, the data can be put to the existing Spreadsheet. In that case, it is required to modify the script.

References:

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

Added:

You want to achieve above goal using google-api-python-client with python.
You have already been able to use the Drive API and Sheets API using python.

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.

Sample script

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)

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.

Thanks for you answer, it looks like it could work, and yes, you got my intentions correctly - but can that code be "translated" to python? I forgot to include it in the question — , Jan 22 '20 at 13:56
https://stackoverflow.com/questions/56434084/google-pydrive-uploading-a-file-to-specific-folder In fact, I was following your answer to that question in the above link - all I need now is to get the document split into pages — , Jan 22 '20 at 14:22
@5Daydreams Thank you for replying. I couldn't notice that you want to achieve your goal using the python script. I should have asked at first time. This is due to my poor English skill. I deeply apologize for this. I added the sample script for python. Could you please confirm it? If that was not the direction you want, I apologize. — Tanaike, Jan 22 '20 at 23:46
I believe it is correct, but sadly I'm running into this error, which prevents me from confirming your answer: `googleapiclient.errors.HttpError: ` — , Jan 23 '20 at 19:34
@5Daydreams Thank you for replying. I apologize for the inconvenience. In your case, it is required to modify the scopes for the access token. But I cannot see your whole script in your question. So although I'm not sure whether this is the direct solution, please set the scopes of `https://www.googleapis.com/auth/spreadsheets` and `https://www.googleapis.com/auth/drive` to your scopes. And please remove the credential file (if you use Quickstart, please remove `token.pickle`.) including the access and refresh token. And please run the script, and authorize the scopes again. — Tanaike, Jan 23 '20 at 21:49
After **48 hours** of errors, I can't express how thankful I am - it worked _exactly as I wanted it to_. You have my eternal gratitude, @Tanaike! Thank you so much! — , Jan 24 '20 at 13:42
@5Daydreams Thank you for replying. I'm glad your issue was resolved. — Tanaike, Jan 24 '20 at 23:23
Something is wrong, actually - my cells all start with a string like this one: " ' " Could you please check if that is happening to you as well? — , Jan 29 '20 at 17:22
@5Daydreams Thank you for replying. I apologize for the inconvenience. I could understand and replicate your situation. So I updated the script. Could you please confirm it? — Tanaike, Jan 29 '20 at 23:30