CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
æHandal
æHandal

Reputation: 21

Appscript save copy as docx

hey guys i found this guide https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/ on transfering data for sheets to google docs but i am trying to get it to save the files as docx since the files will be sent out to customers. Is there any easy way to get it to work?

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Oppdrettsbevis');
  menu.addItem('Generer Oppdrettsbevis', 'createNewGoogleDocs')
  menu.addToUi();

}

function createNewGoogleDocs() {
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('xxxxxxxx');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('xxxxxxxxx')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Data')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[5]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate = new Date(row[3]).toLocaleDateString();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{First Name}}', row[0]);
    body.replaceText('{{Last Name}}', row[1]);
    body.replaceText('{{Position}}', row[2]);
    body.replaceText('{{Hire Date}}', friendlyDate);
    body.replaceText('{{Hourly Wage}}', row[4]);
    
    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index + 1, 6).setValue(url)
    
  })
  
}

Upvotes: 1

Views: 993

Answers (1)

AWoods
AWoods

Reputation: 346

You are asking if it is possible to export a .docx Try Using, from the crossreferenced post beolw the docToDocx() , which I modified to return the URL. Edited to include the Entire code with some Logger lines.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();

}

//include this function
function docToDocx(id) {
  var format = 'docx',
    exportLink =
      'https://docs.google.com/document/d/' + id + '/export?format=' + format,
    blob = UrlFetchApp.fetch(exportLink, {
      headers: {
        Authorization: 'Bearer ' + ScriptApp.getOAuthToken(),
      },
    });
  var thisFile = DriveApp.createFile(blob);
  return thisFile.getUrl()
}

function createNewGoogleDocs() {
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('xx YOUR TEMPLATE ID HERE xx');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('xx YOUR FOLDER ID HERE xx')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Data')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[5])
    {
      Logger.log("Document Made already");
      Logger.log(row[5]);
      return;  //Breaks this iteration of the loop should allow next row
    } 
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate = new Date(row[3]).toLocaleDateString();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{First Name}}', row[0]);
    body.replaceText('{{Last Name}}', row[1]);
    body.replaceText('{{Position}}', row[2]);
    body.replaceText('{{Hire Date}}', friendlyDate);
    body.replaceText('{{Hourly Wage}}', row[4]);
    
    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    Logger.log("The Google Doc: " + doc.getUrl());
    ///Call the docToDocx Function and save the Url: 
    const url = docToDocx( doc.getId() );
    Logger.log("The .docX URL: " + url);
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index + 1, 6).setValue(url);
  }) 
}

After Running the Script I can now see in my Execution log:

Cloud logs
Oct 19, 2022, 12:09:46 PM   Info    The Google Doc: https://docs.google.com/open?id=xx THIS FILE ID xx
Oct 19, 2022, 12:09:48 PM   Info    The .docX URL: https://docs.google.com/document/d/xx THIS FILE ID xx_/edit?usp=drivesdk&ouid=xx THIS TOKEN ID XX&rtpof=true&sd=true
Oct 19, 2022, 12:09:50 PM   Info    The Google Doc: https://docs.google.com/open?id=xx THIS FILE ID xx
Oct 19, 2022, 12:09:52 PM   Info    The .docX URL: https://docs.google.com/document/d/xx THIS FILE ID xx/edit?usp=drivesdk&ouid=xx THIS TOKEN ID XX&rtpof=true&sd=true
Oct 19, 2022, 12:09:54 PM   Info    The Google Doc: https://docs.google.com/open?id=xx THIS FILE ID xx
Oct 19, 2022, 12:09:56 PM   Info    The .docX URL: https://docs.google.com/document/d/xx THIS FILE ID xx/edit?usp=drivesdk&ouid=xx THIS TOKEN ID XX&rtpof=true&sd=true

That shows the Google docs url is different then the supplied uls that will lead to a download of a .docx file. Those docx Urls are also showing in column 5 of the spreadsheet.

The docX function was found at theWizEd's commented crossreference: Converting a GDoc to docx through GAS produces corrupted document

Upvotes: 1

Related Questions