Creating a google doc document from template using data pull from google sheets

Question

I would like to grab data from multiple locations in a spreadsheet and send the data to create a doc from a template.
Any suggestions on how can I simplify or shorten the code?
How much will it affect the performance of the computer?
What I have below is only 30% and if I were to complete the code it will be very very long.

function CreateReport1G() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ReportSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //Get Sheet Report
  var ReportFolder = DriveApp.getFolderById('1XUE25cLJf0BYSknujYlAHJt0dYvAp2yL');
  var oneGradeId = '1J2-Ct9jrfFwfFrJ8apJycm487eNYOFCZvjUX5cBDHDM';
  var C1GD1 = ReportSheet.getRange('D1').getValue();
  var C1GD5 = ReportSheet.getRange('D5').getValue();
  var C1GD8 = ReportSheet.getRange('D8').getValue();
  var C1GD7 = ReportSheet.getRange('D7').getValue();
  var date = ReportSheet.getRange('D6').getValue();
  var C1GD6 = Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD2 = ReportSheet.getRange('D2').getValue();
  var C1GD9 = ReportSheet.getRange('D9').getValue();
  var C1GD10 = ReportSheet.getRange('D10').getValue();
  var C1GD11 = ReportSheet.getRange('D11').getValue();
  var C1GD14 = ReportSheet.getRange('D14').getValue();
  var C1GD15 = ReportSheet.getRange('D15').getValue();
  var C1GD16 = ReportSheet.getRange('D16').getValue();
  var C1GD17 = ReportSheet.getRange('D17').getValue();
  var C1GD18 = ReportSheet.getRange('D18').getValue();
  var C1GD20 = ReportSheet.getRange('D20').getValue();
  var C1GD21 = ReportSheet.getRange('D21').getValue();
  var C1GD22 = ReportSheet.getRange('D22').getValue();
  var C1GD23 = ReportSheet.getRange('D23').getValue();
  var C1GD24 = ReportSheet.getRange('D24').getValue();
  var C1GD29 = Utilities.formatDate(ReportSheet.getRange('D29').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF29 = Utilities.formatDate(ReportSheet.getRange('F29').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD30 = Utilities.formatDate(ReportSheet.getRange('D30').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF30 = Utilities.formatDate(ReportSheet.getRange('F30').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD31 = Utilities.formatDate(ReportSheet.getRange('D31').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF31 = Utilities.formatDate(ReportSheet.getRange('F31').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD32 = Utilities.formatDate(ReportSheet.getRange('D32').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF32 = Utilities.formatDate(ReportSheet.getRange('F32').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD33 = Utilities.formatDate(ReportSheet.getRange('D33').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF33 = Utilities.formatDate(ReportSheet.getRange('F33').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD34 = Utilities.formatDate(ReportSheet.getRange('D34').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF34 = Utilities.formatDate(ReportSheet.getRange('F34').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD35 = Utilities.formatDate(ReportSheet.getRange('D35').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF35 = Utilities.formatDate(ReportSheet.getRange('F35').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD36 = Utilities.formatDate(ReportSheet.getRange('D36').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF36 = Utilities.formatDate(ReportSheet.getRange('F36').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD37 = Utilities.formatDate(ReportSheet.getRange('D37').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF37 = Utilities.formatDate(ReportSheet.getRange('F37').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD38 = Utilities.formatDate(ReportSheet.getRange('D38').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF38 = Utilities.formatDate(ReportSheet.getRange('F38').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD39 = Utilities.formatDate(ReportSheet.getRange('D39').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF39 = Utilities.formatDate(ReportSheet.getRange('F39').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var C1GD40 = Utilities.formatDate(ReportSheet.getRange('D40').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
  var C1GF40 = Utilities.formatDate(ReportSheet.getRange('F40').getValue(), ss.getSpreadsheetTimeZone(), "d MMM YYYY");
  var documentId = DriveApp.getFileById(oneGradeId).makeCopy(ReportFolder).getId();

  DriveApp.getFileById(documentId).setName('Summary Report - Job ' + C1GD1);
  var body = DocumentApp.openById(documentId).getBody();

  body.replaceText('1GD05', C1GD5);
  body.replaceText('1GD08', C1GD8);
  body.replaceText('1GD07', C1GD7);
  body.replaceText('1GD01', C1GD1);
  body.replaceText('1GD06', C1GD6);
  body.replaceText('1GD02', C1GD2);
  body.replaceText('1GD02', C1GD2);
  body.replaceText('1GD09', C1GD9);
  body.replaceText('1GD10', C1GD10);
  body.replaceText('1GD11', C1GD11);
  body.replaceText('1GD14', C1GD14);
  body.replaceText('1GD15', C1GD15);
  body.replaceText('1GD16', C1GD16);
  body.replaceText('1GD17', C1GD17);
  body.replaceText('1GD18', C1GD18);
  body.replaceText('1GD20', C1GD20);
  body.replaceText('1GD21', C1GD21);
  body.replaceText('1GD22', C1GD22);
  body.replaceText('1GD23', C1GD23);
  body.replaceText('1GD24', C1GD24);
  body.replaceText('1GD29', C1GD29);
  body.replaceText('1GF29', C1GF29);
  body.replaceText('1GD30', C1GD30);
  body.replaceText('1GF30', C1GF30);
  body.replaceText('1GD31', C1GD31);
  body.replaceText('1GF31', C1GF31);
  body.replaceText('1GD32', C1GD32);
  body.replaceText('1GF32', C1GF32);
  body.replaceText('1GD33', C1GD33);
  body.replaceText('1GF33', C1GF33);
  body.replaceText('1GD34', C1GD34);
  body.replaceText('1GF34', C1GF34);
  body.replaceText('1GD35', C1GD35);
  body.replaceText('1GF35', C1GF35);
  body.replaceText('1GD36', C1GD36);
  body.replaceText('1GF36', C1GF36);
  body.replaceText('1GD37', C1GD37);
  body.replaceText('1GF37', C1GF37);
  body.replaceText('1GD38', C1GD38);
  body.replaceText('1GF38', C1GF38);
  body.replaceText('1GD39', C1GD39);
  body.replaceText('1GF39', C1GF39);
  body.replaceText('1GD40', C1GD40);
  body.replaceText('1GF40', C1GF40);

  ss.toast("Report has been complied");

}

Cooper · Accepted Answer · 2019-10-17 21:42:40Z

1

This code requires a sheet/tab on a spreadsheet some where I called the sheet "ReportGenerator" and here is a pubic link to a copy of it: ReportGenerator

This will need to be able to access the above spreadsheet. If you change any of the header titles then you will have to make respective changes in the code. That might be some bad news but the good news is that its okay to move the columns around.

function CreateReport1G0() {
  var ss = SpreadsheetApp.getActive();
  var ReportSheet = ss.getActiveSheet(); //Get Sheet Report
  var ReportFolder = DriveApp.getFolderById('1XUE25cLJf0BYSknujYlAHJt0dYvAp2yL');
  var oneGradeId = '1J2-Ct9jrfFwfFrJ8apJycm487eNYOFCZvjUX5cBDHDM';
  var ReportGeneratorSheet=ss.getSheetByName('ReportGenerator')
  var rg=ReportGeneratorSheet.getDataRange();
  var vA=rg.getValues();
  var hA=vA[0];
  var hObj={};
  var hObj={};
  var reportObj={};
  var replaceObj={};
  DriveApp.getFileById(documentId).setName('Summary Report - Job ' + C1GD1);
  var body = DocumentApp.openById(documentId).getBody();
  hA.forEach(function(name,i){hObj[name]=i;});
  vA.forEach(function(r,i){
    switch (r[hObj.Action]) {
      case 'getValue':
        body.replaceText(r[hObj.DocCode],r[hObj.cidx]);
        break;
      case 'formatDate':
        body.replaceText(r[hObj.DocCode],Utilities.formatDate(new Date(r[hObj.cidx]),ss.getSpreadsheetTimeZone(),"d MMM YYYY"));
        break;
    }
  })
}

The information in columns A,B & G actually came from you. The below function calculates the row,col,ridx and cidx which are then used in the above function to replace data in the document.

function calculateRowColAndIndices() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("ReportGenerator");
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var hObj={};
  hA.forEach(function(name,i){
    hObj[name]=i;
  });
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var vA=rg.getValues();
  vA.forEach(function(r,i){
    r[hObj.col]=sh.getRange(r[hObj.A1Not]).getColumn();
    r[hObj.row]=sh.getRange(r[hObj.A1Not]).getRow();
    r[hObj.cidx]=Number(r[hObj.col])-1;
    r[hObj.ridx]=Number(r[hObj.row])-1;
  })
  rg.setValues(vA);
}

I suspect that it's unlikely that this code will run the first time as I have no data to test it with. You welcome to utilize that pubic file to get it to work. If you need additional assistance feel free to ask.

answered Oct 17, 2019 at 21:42

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Norvin Over a year ago

The result returns all replaced text as '3' and date and times as '1 Jan 1970'

Norvin Over a year ago

How is this function executed 'function calculateRowColAndIndices()'?

Cooper Over a year ago

In the Report Generator Sheet you provide DocCode,A1Not and Action and then this function calculate all of the indices for you. You only have to do this whenever that data changes. I suppose you could do it at the beginning of CreateReport1G0() every time also. The CreateReport1G0() function actually reads that table and that's how it determines how to run the script. That table is kind of control panel.

Collectives™ on Stack Overflow

Creating a google doc document from template using data pull from google sheets

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related