Mail Merge using Google sheet and Google Docs

Here I have tried to keep inventory of RTI applications and from this data tried to generate letters from Template.

    The following script effectively merges data from the spreadsheet into the template document, creating personalized copies for each row of data. It ensures that the final document is saved and closed properly.

function myFunction() {

  try {

    var DocTemplateId = "1QwycRODkptuV5amXwVBlgEusdYJG6Wm5fR8v2wSaST0";

    var DocFinalId = "16uRPbD1ZGKmxWOOTGaFkyGyXynjpSPM_SOV0LPZPDxM";

    var WSId = "1rHWYXX4I0yqZvcYhjZ4FDljmz1P982klEwNXivHTYBE";

    var DocTemplate = DocumentApp.openById(DocTemplateId);

    var DocFinal = DocumentApp.openById(DocFinalId);

    var WS = SpreadsheetApp.openById(WSId).getSheetByName("Index");

    // Ensure valid range

    var lastRow = WS.getLastRow();

    if (lastRow <= 1) {

      throw new Error("Spreadsheet has no data or only one row.");

    }

    var numRows = 39

    var data = WS.getRange(444, 1, numRows, 28).getValues();

    var TemplateParagraphs = DocTemplate.getBody().getParagraphs();

    data.forEach(function(r, index) {

      if (index % 50 == 0 && index > 0) {  // Save and close every 50 iterations

        DocFinal.saveAndClose();

        DocFinal = DocumentApp.openById(DocFinalId);  // Reopen the document

      }

      createMailMerge(

        r[2],

        r[3],

        r[5],

        r[7],

        r[16],

        r[18],

        r[26],

        r[27],

        TemplateParagraphs,

        DocFinal

      );

    });

    // Save the final document

    DocFinal.saveAndClose();

  } catch (e) {

    Logger.log("Error in myFunction: " + e.message);

  }

}

function createMailMerge(अर्जदाराचे_नाव, अर्जदाराचे_पत्ता, आरटीआय_अर्जावरील_दिनांक, माहिती_अधिकाऱ्याकडे_अर्ज_मिळाल्याचा_दिनांक, अपील_दिनांक, अपील_वेळ, Related_Office,Information_Seeked, TemplateParagraphs, DocFinal) {

  try {

    TemplateParagraphs.forEach(function(P) {

      DocFinal.getBody().appendParagraph(

        P.copy()

          .replaceText("{अर्जदाराचे नाव}", अर्जदाराचे_नाव)

          .replaceText("{अर्जदाराचे पत्ता}", अर्जदाराचे_पत्ता)

          .replaceText("{आरटीआय अर्जावरील दिनांक}", आरटीआय_अर्जावरील_दिनांक)

          .replaceText("{माहिती अधिकाऱ्याकडे अर्ज मिळाल्याचा दिनांक}", माहिती_अधिकाऱ्याकडे_अर्ज_मिळाल्याचा_दिनांक)

          .replaceText("{अपील दिनांक}", अपील_दिनांक)

          .replaceText("{अपील वेळ}", अपील_वेळ)

          .replaceText("{Related Office}", Related_Office)

          .replaceText("{Information Seeked}", Information_Seeked)

      );

    });

  } catch (e) {

    Logger.log("Error in createMailMerge: " + e.message);

  }

}

Script Explanation:

The provided script, myFunction(), performs a mail merge operation using Google Docs and Google Sheets. It utilizes a template document, a data source spreadsheet, and creates personalized copies of the template for each row of data in the spreadsheet.

Function Breakdown:

myFunction():

1) Retrieving Document and Spreadsheet Objects:

DocTemplateId, DocFinalId, and WSId variables store the IDs of the template document, final document, and data source spreadsheet, respectively.

DocTemplate, DocFinal, and WS variables are created using DocumentApp.openById() and SpreadsheetApp.openById() to access the respective document and spreadsheet.

WS is further refined to access the "Index" sheet using getSheetByName().

2)Validating Spreadsheet Data:

lastRow is obtained using WS.getLastRow() to determine the number of rows in the spreadsheet.

An error is thrown using throw new Error() if lastRow is less than or equal to 1, indicating insufficient data.

3) Processing Spreadsheet Data:

numRows is set to 39, indicating the number of rows to be processed.

data is assigned the values from the specified range (getRange()) in the spreadsheet.

TemplateParagraphs is obtained from the template document's body using getBody().getParagraphs().

Iterating through Data and Performing Mail Merge:

The forEach() function iterates through each row (r) in the data array.

An if statement checks if the index (index) is divisible by 50 and greater than 0.

If the condition is true:

DocFinal is saved and closed using saveAndClose().

DocFinal is reopened using DocumentApp.openById() to ensure a fresh copy for subsequent merges.

The createMailMerge() function is called, passing the relevant data fields (अर्जदाराचे_नाव, etc.) and document objects (TemplateParagraphs, DocFinal).

4) Saving the Final Document:

After processing all rows, DocFinal is saved and closed using saveAndClose().

createMailMerge(dataFields, TemplateParagraphs, DocFinal):

5) Iterating through Template Paragraphs:

The forEach() function iterates through each paragraph (P) in the TemplateParagraphs array.

For each paragraph:

A copy of the paragraph (P.copy()) is created.

Each placeholder in the template is replaced with the corresponding data value using replaceText():

{अर्जदाराचे नाव} is replaced with अर्जदाराचे_नाव

Similarly, other placeholders are replaced with their respective data fields.

The modified paragraph is appended to the final document's body using DocFinal.getBody().appendParagraph().

6) Error Handling:

An internal try-catch block is used to handle any errors that may occur during paragraph replacement.