Search code examples
javascriptgoogle-apps-scriptgoogle-appsheet

I can't get this script to replace the values that are inside of the table


I have a script that I would like to use to replace values in agoogle doc template with values from a google sheet, The script is recognising the varibles and can use them to create unique file names based on the info in the google sheet but it seems to be struggliung to edit the values in the google docs templates table

function createDocument() {
  var headers = Sheets.Spreadsheets.Values.get('1yugsklotVyaj2ecZT0ZJip7XyGmEq1zxaK_6tcA1pa4', 'B1:AD1');
  var tactics = Sheets.Spreadsheets.Values.get('1yugsklotVyaj2ecZT0ZJip7XyGmEq1zxaK_6tcA1pa4', 'A2:AD2');
  var templateId = '1xcIm53z5FAX4pJLCoJVdDYDgVhsRkXO3uqZgsNDfO_w';

  for(var i = 0; i < tactics.values.length; i++){

    var Brand = tactics.values[i][1];
    var Model = tactics.values[i][2];
    var Size = tactics.values[i][3];
    var Groupset = tactics.values[i][5];
    var Price = tactics.values[i][10];
    var Frame = tactics.values[i][11];
    var Fork = tactics.values[i][12];
    var Gears = tactics.values[i][13];
    var Cassette = tactics.values[i][14];
    var BrakeType = tactics.values[i][15];
    var Handlebar = tactics.values[i][16];
    var Seatpost = tactics.values[i][17];
    var Saddle = tactics.values[i][18];
    var WheelMaterial = tactics.values[i][19];
    var RimType = tactics.values[i][20];

   //Make a copy of the template file
    var documentId = DriveApp.getFileById(templateId).makeCopy().getId();

    //Rename the copied file
    DriveApp.getFileById(documentId).setName( Brand + Model + ' Spec Card');

    //Get the document body as a variable
    var body = DocumentApp.openById(documentId).getBody();

    body.replaceText('##[Brand]##',Brand)
    body.replaceText('##[Model]##', Model)
    body.replaceText('##[Size]##', Size) 
    body.replaceText('##[Groupset]##', Groupset)
    body.replaceText('##[Price]##', Price)
    body.replaceText('##[Frame]##', Frame)
    body.replaceText('##[Fork]##', Fork)
    body.replaceText('##[Gears]##', Gears)
    body.replaceText('##[Cassette]##', Cassette)
    body.replaceText('##[Brake Type]##', BrakeType)
    body.replaceText('##[Handlebar]##', Handlebar)
    body.replaceText('##[Seatpost]##', Seatpost)
    body.replaceText('##[Saddle]##', Saddle)
    body.replaceText('##[Wheel Material]##', WheelMaterial)
    body.replaceText('##[RimType]##', RimType)
  }
 
}

I tried to replace values inside the table, but it only edit the file name


Solution

  • I believe body.replaceText('##[Brand]##',Brand) is interpreting the open and close square brackets as regex characters.

    Replace (and all other brackets)

    body.replaceText('##[Brand]##',Brand)
    

    With

    body.replaceText('##\\[Brand\\]##',Brand)