How to convert table from google sheet into paragraph in google document?-CodePudding

I'm new to the google app script. I was trying to create a google document using google app script, to help me to convert data from the table (from spreadsheet) into paragraphs in google doc.

This is the sample of the data source, and this is the expected output that I want.

What script should I use to make this happen?

thank you for answering the question.

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the values from Spreadsheet and put them to the Document with the format using Google Apps Script.

In your situation, how about the following flow?

Retrieve values from Spreadsheet and create an object.
Create new Google Document.
Using the object, each value is put to the created Document with the format.

When this flow is reflected in a script, it becomes as follows.

Sample script:

In this sample, please copy and paste it to the script editor of Google Spreadsheet including the values. And, if you want to change title, sectionTitle and head, please modify the script. And, run the function myFunction.

function myFunction() {
  const title = "DOCUMENT TITLE";
  const sectionTitle = "SECTION TITLE";
  const head = ["ID: ", "EMPLOYEE NAME: "];

  // 1. Retrieve values from Spreadsheet and create an object.
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getValues();
  const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}));

  // 2. Create new Google Document.
  const doc = DocumentApp.create("temp");

  // 3. Using the object, each value is put to the created Document with the format.
  const lineBreak = _ => body.appendParagraph("").editAsText().setBold(false).setFontSize(11);
  const body = doc.getBody()
  body.appendParagraph(title).setHeading(DocumentApp.ParagraphHeading.HEADING1).editAsText().setBold(true).setFontSize(20);
  lineBreak();
  res.forEach(e => {
    headers.forEach((h, j) => {
      if (e[h]) {
        if (j < 2) {
          body.appendParagraph(head[j]   e[h]).editAsText().setBold(0, head[j].length - 1, true).setFontSize(11);
          if (j == 1) {
            lineBreak();
            body.appendParagraph(sectionTitle).editAsText().setBold(true).setFontSize(14);
            lineBreak();
          }
        } else if (j == 2) {
          body.appendParagraph(e[h]).setHeading(DocumentApp.ParagraphHeading.HEADING2).editAsText().setBold(true).setFontSize(12);
          lineBreak();
        } else {
          body.appendParagraph(e[h]);
          lineBreak();
        }
      }
    });
  });
}

When you run this script, a new Google Document is created and the values from Spreadsheet are put to the Document. You can see it at the root folder.

Note:

This sample script is from your sample Google Spreadsheet and Google Document. When those are changed, this script might not be able to be used. Please be careful about this.

References:

Added:

About your following new question,

Thanks! This is really helpful. I was wondering if let's say I want to create 1 document for Johnny Depp, and 1 document for Michael Page, and let's say I have more list of names in the table and I want to create 1 document for each person. How will it be?

From your new question in your comment, I cannot understand the filename of new Document and the values of title, sectionTitle and head. So in this case, please modify them for your actual situation.

How about the following sample script?

Sample script:

function myFunction() {
  const title = "DOCUMENT TITLE";
  const sectionTitle = "SECTION TITLE";
  const head = ["ID: ", "EMPLOYEE NAME: "];

  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getValues();
  const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}));

  const lineBreak = body => body.appendParagraph("").editAsText().setBold(false).setFontSize(11);
  let body;
  res.forEach((e, i) => {
    headers.forEach((h, j) => {
      if (e[h]) {
        if (j < 2) {
          if (j == 0) {
            const doc = DocumentApp.create(e["Legal Name"]);
            body = doc.getBody()
            body.appendParagraph(title).setHeading(DocumentApp.ParagraphHeading.HEADING1).editAsText().setBold(true).setFontSize(20);
            lineBreak(body);
            body.appendParagraph(head[j]   e[h]).editAsText().setBold(0, head[j].length - 1, true).setFontSize(11);
          } else if (j == 1) {
            body.appendParagraph(head[j]   e[h]).editAsText().setBold(0, head[j].length - 1, true).setFontSize(11);
            lineBreak(body);
            body.appendParagraph(sectionTitle).editAsText().setBold(true).setFontSize(14);
            lineBreak(body);
          }
        } else if (j == 2) {
          body.appendParagraph(e[h]).setHeading(DocumentApp.ParagraphHeading.HEADING2).editAsText().setBold(true).setFontSize(12);
          lineBreak(body);
        } else {
          body.appendParagraph(e[h]);
          lineBreak(body);
        }
      }
    });
  });
}