How to get paragraphs in google doc from google spreadsheet table and create a link for document?

user_17268030 0
Viewed 248

This is a continuation of my previous question I posted. I am new to this site and also to google script, please apologize my knowledge on script. I have two questions that I want to ask:

  1. I was assigned a task from my lead to convert table from spreadsheet into paragraphs in a google doc.

Basically, I was assigned to turn this set of data, into the expected output.

While Employee_ID and Worker information is contained only 1 information, Goal, Description, Self-Evaluation, and Manager_Comment might have multiple rows and each person might have different numbers (i.e. some person might only have 1 goal, some others might have 2, 3 or even more). However, I was tasked to create one file for each person, thus I have to put all the goals, description, self-evaluation, and manager_comment of each person in one document.

What script should I use to make this happen?

  1. Everytime I generate the document, I was also expecting to generate a link of every document and put it into the tab in a spreadsheet. With the help of google script, I hope I can automatically print each employee's document link in this tab. I put some examples of what I expect here. Please advice what script that I can use to make this happen.

thank you for answering the question. I highly appreciate it!

google-apps-script google-sheets google-docs javascript
1 Answers

In this case, in order to achieve your goal, I modified the sample script of your previous question as follows. The modification points are as follows.

  • In order to use the existing script, I converted the value of res for your new situation.
  • I added the paragraph name of each paragraph.
  • In order to include your 2nd request, the values of "Employee_ID", "Worker" and the URL of created Document are returned from the loop and those values are put to "Link" sheet.

When these are reflected to the sample script, it becomes as follows.

Sample script:

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

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [headers, ...rows] = ss.getSheetByName("Raw Data").getDataRange().getValues();
  let w = "";
  const res = rows.map((r, i) => {
    const obj = headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {});
    if (i == 0) {
      w = obj["Worker"];
      return obj;
    }
    if (w != obj["Worker"]) {
      w = obj["Worker"];
    } else {
      obj["Worker"] = "";
      obj["Employee_ID"] = "";
    }
    return obj;
  });

  const lineBreak = body => body.appendParagraph("").editAsText().setBold(false).setFontSize(11);
  let body;
  const values = res.flatMap((e, i) =>
    headers.reduce((ar, h, j) => {
      if (e[h]) {
        if (j < 2) {
          if (j == 0) {
            const doc = DocumentApp.create(e["Worker"]);
            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);
            ar.push([e["Employee_ID"], e["Worker"], doc.getUrl()]);
          } 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(h + ":");
          body.appendParagraph(e[h]);
          lineBreak(body);
        }
      }
      return ar;
    }, [])
  );

  const dstSheet = ss.getSheetByName("Link");
  dstSheet.getRange("A2:C").clearContent();
  dstSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

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.

  • Unfortunately, I cannot find "Employee_Comment:" in your sample output Document. In this case, is that "Self_Evaluation"? This can be found from the header row of the sample Spreadsheet. My proposed script uses this value.

References:

user_71086530
Related