Invalid argument: replacement error when populating google doc using google script-CodePudding

I have written a code to populate data from a spreadsheet into a google doc and save it to drive using g-sript. Here is the code for the same :

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Invoice creator');
  menu.addItem('Generate Invoice', 'invoiceGeneratorFunction');
  menu.addToUi();
}


function invoiceGeneratorFunction() {
  const invoiceTemplate = DriveApp.getFileById('125NPu-n77F6N8hez9w63oSzbWrtryYpRGOkKL3IbxZ8');
  const destinationFolder = DriveApp.getFolderById('163_wLsNGkX4XDUiSOcQ88YOPe3vEx7ML');
  const sheet_invoice = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('New Invoice Sheet');

  const rows = sheet_invoice.getDataRange().getValues();
  Logger.log(rows);

  rows.forEach(function(row, index) {
    if (index === 0) return;
    if (row[12] != "") return;

    const copy = invoiceTemplate.makeCopy(`${row[1]} VIN Number: ${row[2]}`,destinationFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    var friendlyDateBilled = new Date(row[0]).toLocaleDateString();
    var friendlyDateDelivery = new Date(row[3]).toLocaleDateString();

    body.replaceText('{{Date Billed}}',friendlyDateBilled);
    body.replaceText('{{Customer Name}}',row[1]);
    body.replaceText('{{VIN Number}}',row[2]);
    body.replaceText('{{Date of Delivery}}',friendlyDateDelivery);
    body.replaceText('{{Package}}',rows[4]);
    body.replaceText('{{Price}}',rows[5]);
    body.replaceText('{{Output CGST}}',rows[6]);
    body.replaceText('{{Output SGST}}',rows[7]);
    body.replaceText('{{Discount}}',rows[8]);
    body.replaceText('{{Total Price}}',rows[9]);
    body.replaceText('{{Balance}}',rows[10]);
    body.replaceText('{{Remarks}}',rows[11]);

    doc.saveAndClose();

    const url = doc.getUrl();
    sheet_invoice.getRange(index 1, 13).setValue(url);

  })

}

I have created a menu button for the script to run. But when i run it I get an error saying :

Exception: Invalid argument: replacement at unknown function at invoiceGeneratorFunction(Code:17:8)

(Here line 32 is body.replaceText('{{Package}}',rows[4]); and line 17 is the start of forEach)

Interestingly when I comment out the rest of body.replaceText lines after that line, the code works. I can't understand what the problem is, if it's working if I comment out the lines.

CodePudding user response：

In your script, rows is 2 dimensional array retrieved with sheet_invoice.getDataRange().getValues(). When I saw your loop, after the line of body.replaceText('{{Package}}',rows[4]);, rows is used. In this case, rows[4] is 1-dimensional array. It is required to be the string for the arguments of replaceText(searchPattern, replacement). I think that this might be the reason for your issue. In order to remove this issue, how about the following modification?

From:

body.replaceText('{{Package}}',rows[4]);
body.replaceText('{{Price}}',rows[5]);
body.replaceText('{{Output CGST}}',rows[6]);
body.replaceText('{{Output SGST}}',rows[7]);
body.replaceText('{{Discount}}',rows[8]);
body.replaceText('{{Total Price}}',rows[9]);
body.replaceText('{{Balance}}',rows[10]);
body.replaceText('{{Remarks}}',rows[11]);

To:

body.replaceText('{{Package}}',row[4]);
body.replaceText('{{Price}}',row[5]);
body.replaceText('{{Output CGST}}',row[6]);
body.replaceText('{{Output SGST}}',row[7]);
body.replaceText('{{Discount}}',row[8]);
body.replaceText('{{Total Price}}',row[9]);
body.replaceText('{{Balance}}',row[10]);
body.replaceText('{{Remarks}}',row[11]);

Note:

I'm not sure about your actual values of rows. So I'm not sure whether the values of row[4] to row[11] are what you want. If those values are not the values you expect, please check your Spreadsheet again.

Reference:

replaceText(searchPattern, replacement)