javascript - How to optimise Apps Script code by using arrays to pull data from Google Sheets and format it?

Question

I have a script that takes data from a gsheet and replaces placeholders on gdoc. I am looking to optimise the script by using arrays instead.

This is a sample of my gsheet (the original gsheet spans 1000+ rows and 15+ columns),

Original script:

function generategdoc() {

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();

  for (var i =2;i<=lr;i++){

    if(ss.getRange(i, 1).getValue()){    

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

  var Client = ss.getRange(i, 2).getValue();
  var Amount = ss.getRange(i, 3).getValue();
    var AmountFormat = Amount.toFixed(2).replace(/(d)(?=(d{3})+.)/g, '$1,');  

  var Date = ss.getRange(i, 4).getValue();
    var temp = new Date(Date)
    var DateFormat = Utilities.formatDate(temp, "GMT+0400", "dd MMM yyyy")

  //Rename the copied file
  DriveApp.getFileById(documentId).setName(Client);  

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

  body.replaceText('##Client##', Client).replaceText('##Amount##', AmountFormat).replaceText('##Date##', DateFormat)
    }
    else {}
  }
}

As you can see this script will only run for all the rows which have been checkboxed TRUE.

Attempt 1 at optimising:

function optimise() {

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();

  var rng = ss.getRange("A1:"+"D"+lr).getValues();  //Creation of Array

  for (var i =2;i<=lr;i++){

    if(ss.getRange(i, 1).getValue()){
      var Client = rng[i-1][1];
      var Amount = rng[i-1][2];
      var Date = rng[i-1][3];

  var documentId = DriveApp.getFileById('FileID').makeCopy().getId();

      DriveApp.getFileById(documentId).setName(Client);  

  var body = DocumentApp.openById(documentId).getBody();
      body.replaceText('##Client##', Client).replaceText('##Amount##', Amount).replaceText('##Date##', Date)
          }
    else {}
  }
}

Question:
I was able to format the original script for Amount and Date. How can I have the same formatting for arrays? As I cant apply formatDate(class utilities) and toFixed to my variables anymore because they are now arrays.

Answer 1

You can try something like this. But it may not make any difference because creating files takes a long time.

function optimise() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var vA= ss.getRange(1,1,ss.getLastRow(),4).getValues();
  var file = DriveApp.getFileById('FileID');
  for(var i=0;i<vA.length;i++){
    if(vA[i][0]){
      var Client=vA[i][1];
      var Amount=vA[i][2];
      var Date=vA[i][3];
      DriveApp.getFileById(file.makeCopy().getId()).setName(Client);  
      var body=DocumentApp.openById(documentId).getBody();
      body.replaceText('##Client##', Client).replaceText('##Amount##', Amount).replaceText('##Date##', Date)
    }
  }
}

I wonder if you need to saveAndClose() the document.