CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
Tyler Read
Tyler Read

Reputation: 3

Is there a function to convert a google doc to a google spreadsheet?

I need a way to transfer the data from a google document to be transferred over to a google spreadsheet (kind of backwards, I know). I need the line breaks in the document to be equivalent to starting a new cell right below. i.e. each line in the google doc has its own cell.

I've tried getting the body text from the google doc and setting the first cell as that variable but that only pastes the data in the single A1 cell (kind of want it to be similar to the way if you copy and paste doc body text into the A1 cell it will populate cells in the A column all the way down)

var body = openedFile.getBody().getText();
var newSpreadsheet = SpreadsheetApp.create('TEST').getId();
var testSpreadsheet = SpreadsheetApp.openById(newSpreadsheet);
testSpreadsheet.getRange('A1').setValue(bodyAll);

Upvotes: 0

Views: 160

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • You want to put each paragraph of Document to the cells of Spreadsheet which was created in the script.
    • From your script, your Google Document has only texts. Your Google Document doesn't include tables, lists, images and so on.

If my understanding is correct, how about this modification?

Pattern 1:

In this pattern, body of var body = openedFile.getBody().getText() is splitted by \n. Then, the values are put to the created Spreadsheet.

Modifed script:

var openedFile = DocumentApp.openById("###"); // Please set Document ID here.
var body = openedFile.getBody().getText();
var bodyAll = body.split("\n").reduce(function(ar, e) {
  if (e) ar.push([e]); // If you want to include the empty paragraph, please modify to ar.push([e]);
  return ar;
}, []);
var newSpreadsheet = SpreadsheetApp.create('TEST');
newSpreadsheet.getSheets()[0].getRange(1, 1, bodyAll.length, bodyAll[0].length).setValues(bodyAll);

Pattern 2:

In this pattern, the paragraphs are retrieved from the body of Document, and each text is retrieved. Then, the values are put to the created Spreadsheet.

Modifed script:

var openedFile = DocumentApp.openById("###"); // Please set Document ID here.
var body = openedFile.getBody();
var bodyAll = body.getParagraphs().reduce(function(ar, e) {
  var temp = e.getText();
  if (temp) ar.push([temp]); // If you want to include the empty paragraph, please modify to ar.push([temp]);
  return ar;
}, [])
var newSpreadsheet = SpreadsheetApp.create('TEST');
newSpreadsheet.getSheets()[0].getRange(1, 1, bodyAll.length, bodyAll[0].length).setValues(bodyAll);

Note:

  • In this script, the empty paragraphs are ignored. If you want to include the empty paragraphs, please modify the script like the comment.

References:

If above scripts didn't resolve your issue, I apologize. At that time, in order to correctly understanding your situation, can you provide a sample Document and a sample Spreadsheet of the result you want? Of course, please remove your personal information from them.

Upvotes: 2

Related Questions