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.

As the formatDate and toFixed are methods of Class Range. What makes you say that? Provide documentation for this claim — TheMaster
– TheMaster, Commented May 6, 2020 at 5:10
I have updated the statement - I meant to say that I couldn't apply formatDate and toFixed methods. I have also updated the Question to ask one instead. — Mishal
– Mishal, Commented May 6, 2020 at 6:18
As per your documentation link, none of the parameters require range type. They are of Date and string types. Your array type is object[][] (as written in the getValues() documentation), which consists of Date and string types. There is nothing to suggest that you can't apply formatDate() to values inside arrays. — TheMaster
– TheMaster, Commented May 6, 2020 at 8:12
Yes you are right I shouldn't have assumed. It's the getDisplaceValues() that I should have been using. Thank you for your patience and your guidance. I appreciate your teaching ethics @TheMaster! — Mishal
– Mishal, Commented May 6, 2020 at 9:11

Aerials · Accepted Answer · 2020-05-06 13:48:27Z

2

I believe you can optimize your code by using the appropriate method(s) to preserve the text format of your Google sheet values.

Look into using the following method(s) of class Range from SpreadsheetApp service.

Method getDisplayValue() returns the displayed value of the cell in the range. The value is a String. The displayed value takes into account date, time and currency formatting, including formats applied automatically by the spreadsheet's locale setting. Empty cells return an empty string.

This should optimize your code by removing the necessity of regex. If I didn't understand your issue correctly, please leave a comment.

References:

edited May 6, 2020 at 13:48

answered May 6, 2020 at 7:52

Aerials

4,4691 gold badge20 silver badges20 bronze badges

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

Comments

Cooper · Accepted Answer · 2020-05-05 21:04:28Z

2

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.

answered May 5, 2020 at 21:04

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

1 Comment

Mishal Over a year ago

I have updated the if statement for (var i =2;i<=lr;i++){ if(rng[i-1][0]){ however would it still be possible to minimise the calls? Also the format doesn't come through as per the original script (see question 2). If there a way to fix the formats in the arrays?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related