Home>

I would like to print the personal data entered in the google spreadsheet as a document.
I used to use the mail merge function of office, but now I would like to implement it using google's GAS code if possible if I have many opportunities to use google spreadsheets and documents.
Here is the code I wrote as far as I can understand

function insertName () {
  const sheet = SpreadsheetApp.getActiveSheet ();
  const lastRow = sheet.getLastRow ();
  const DOC_URL ='https://docs.google.com/document/d/1bb7Q7rrxL5rRjsnfALT9MGslGfHG7VMY/edit';
  const doc = DocumentApp.openByUrl (DOC_URL);
  const docText = doc.getBody (). getText ();
  for (let i = 2;i<= lastRow;i ++) {
    const kana = sheet.getRange (i, 1) .getValue ();// Furigana
    const name = sheet.getRange (i, 2) .getValue ();// name
    const type = sheet.getRange (i, 3) .getValue ();// type
    const alliance = sheet.getRange (i, 4) .getValue ();// Partner
    const tel = sheet.getRange (i, 5) .getValue ();// Phone number
    const mail = sheet.getRange (i, 6) .getValue ();// Email address
    const sex = sheet.getRange (i, 7) .getValue ();// Gender
    const year = sheet.getRange (i, 8) .getValue ();// Age
    const schoolyear = sheet.getRange (i, 9) .getValue ();// grade
    const participationhistory = sheet.getRange (i, 10) .getValue ();// Participation history
    const level = sheet.getRange (i, 11) .getValue ();// Level
    const brother = sheet.getRange (i, 12) .getValue ();// Brother
    const bus = sheet.getRange (i, 13) .getValue ();// Bus desired
    const busrute = sheet.getRange (i, 14) .getValue ();// Bus route
    const busstop = sheet.getRange (i, 15) .getValue ();// stop
    const dm = sheet.getRange (i, 16) .getValue ();// DM hope
    const body = docText
      .replace ('{furigana}', kana)
      .replace ('{name}', name)
      .replace ('{type}', type)
      .replace ('{partner}', alliance)
      .replace ('{phone number}', tel)
      .replace ('{email address}', mail)
      .replace ('{gender}', sex)
      .replace ('{age}', year)
      .replace ('{grade}', schoolyear)
      .replace ('{participation history}', participation history)
      .replace ('{level}', level)
      .replace ('{brother}', brother)
      .replace ('{bus hope}', bus)
      .replace ('{bus route}', busrute)
      .replace ('{stop}', bus stop)
      .replace ('{DM hope}', dm);
    console.log (body);
  }
}
google apps script

Currently the log is output, but I do not know how to print out all the data at once with the code to write to the document.
I am struggling. Could anyone please tell me? ??

Click here for spreadsheet
https://docs.google.com/spreadsheets/d/124v0viFAxSvvAN9Zp3cv14V-p8_MmbxLCiPTUvfBx2g/edit#gid=0
Click here for documentation
https://docs.google.com/document/d/1bb7Q7rrxL5rRjsnfALT9MGslGfHG7VMY/edit

  • Answer # 1

    The shared "https://docs.google.com/document/d/1bb7Q7rrxL5rRjsnfALT9MGslGfHG7VMY/edit" is .docx, so "currently logged" is false in this code.

    If you want to output to a document, the format will be corrupted unless you replaceText directly.

    I don't think it is possible to output print commands directly from Google Apps Script to the printer. Therefore, I try to create a document in the folder at the time of execution. I feel like I can put it out in PDF.

    I made a promise, but I couldn't reduce the execution time. It takes about 30 seconds for these 12 data items. As the number of students increases, it may be difficult to complete in 5 minutes (because adding PDF conversion will take more time).

    function insertName () {
        const templateDocId = "";
        const keys = ['{Furigana}','{Name}','{Type}','{Partner}','{Phone number}','{Email address}','{Gender}',' {Age}','{Grade}','{Participation history}','{Level}','{Brothers}','{Bus hope}','{Bus route}','{Stop}', '{DM hope}'];
        const template = DriveApp.getFileById (templateDocId);
        if (template.getMimeType ()! == "application/vnd.google-apps.document") return;
        const folder = DriveApp.createFolder ((new Date ()). toISOString ());
        SpreadsheetApp.getActiveSheet (). GetDataRange (). GetValues ​​(). Slice (1) .forEach ((r, i) =>{
            if (r [0] === "") return;
            const body = DocumentApp.openById (template.makeCopy (`${r [0]} ${i}`, folder) .getId ()). getBody ();
            keys.forEach ((k, j) =>body.replaceText (k, r [j]));
        });
    }