Is there a Google App Script to export rows into Google Doc text?

Jonathan Terluk 0
Viewed 42

I am struggling to write a Google App Script that allows me to combine cell values across rows in Google Sheets into text that appears in a separate Google Doc (and that ideally allows the formatting to be kept). Example of what I am after :

I have done a mail merge before, but I don't want separate documents for each row. I tried to edit an old script and use that, but it just created >100 new documents in my Google Drive, so something is not going right. I just want a paragraph break to separate each row.

Ideally, I'd love a version of the script that just prints to the same document every time it is run.

Any ideas? Would love any examples, if you have ideas! Really appreciated.

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

Adding Bold and preserving hyperlink in Doc

function writeLines() {
  const bold = {};
  bold[DocumentApp.Attribute.BOLD] = true;
  const normal = {};
  normal[DocumentApp.Attribute.BOLD] = false;
  const ss = SpreadsheetApp.openById("ssid");
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues()
  const fs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getFormulas()
  const cs = vs.map((r,i) => [r[0],r[1],fs[i][2]]);
  const doc = DocumentApp.getActiveDocument();
  const b = doc.getBody();
  cs.forEach((r, i) => {
    let a = r[2].match(/^[^"]+\("([^"]+)","([^"]+)/);
    b.appendParagraph(`${r[0]} `).setAttributes(bold);
    b.appendParagraph(` ${r[1]}`).setAttributes(normal).merge();
    b.appendParagraph(` ${a[2]}`).setLinkUrl(a[1]).merge();
  });
}

The spreadsheet is set up with Text in columns A and B and a hyperlink formula in column C.

Similar to this:

enter image description here

and the ouptut looks like this:

enter image description here

user30hu0
Related