CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
BigBenne
BigBenne

Reputation: 117

Speed up a script Gscript

I'm looking for a way to improve performance of my code.

My function has to convert String date format : 20200208 to 08/02/2020.

function convertToDate(sheet,col){
  var s = sheet;
  var col = col;
  var newVal = new Array();
  var oldVal = s.getRange(2,col,s.getLastRow()-1).getDisplayValues();
  for (var i =0;i<=oldVal.length-1;i++){
    var val = oldVal[i].toString();
    var annee = val.slice(0,4);
    var mois = val.slice(4,6);
    var jour = val.slice(6,8);
    //Logger.log(" Day: " + jour + "\nMonth : "+mois + "\nYear: "+annee);
    var update = Utilities.formatDate(new Date(annee,mois-1,jour),Session.getScriptTimeZone(),"dd/MM/yyyy");
    newVal.push(update);
    //Logger.log(newVal[i])
  }
  
  for (var j = 2; j<= s.getLastRow();j++){
    s.getRange(j,col).setValue(newVal[j-2]);
  }
 
 return Logger.log("end");

I've already read the Best Practises here : https://developers.google.com/apps-script/guides/support/best-practices

But I don't know how to applicate theses means in my function.

If sombody is able to correct me on some points, it could be very usefull !

Sincerely,

BigBenne

Upvotes: 0

Views: 96

Answers (2)

doubleunary
doubleunary

Reputation: 18784

Your script is running slowly because use are writing cells one at a time. Use setValues() instead of setValue() to write them all at once. Try this:

function convertToDate(sheet, column) {
  const firstRow = 2;
  const range = sheet.getRange(firstRow, column, sheet.getLastRow() - firstRow + 1, 1);
  const dateFormat = 'dd/MM/yyyy';
  const timezone = sheet.getParent().getSpreadsheetTimeZone();
  const newValues = range
    .getDisplayValues()
    .map(row => row.map(string => {
      const annee = string.slice(0, 4);
      const mois = string.slice(4, 6);
      const jour = string.slice(6, 8);
      return Utilities.formatDate(new Date(annee, mois - 1, jour), timezone, dateFormat);
    }));
  range.setValues(newValues);
}

Note that this function will not actually convert the values to dates but to text strings that look like dates. To get real numeric dates, use return new Date(annee, mois - 1, jour); and format the results in the spreadsheet through Format > Number > Date or a custom number format of your choice.

See this answer for an explanation of how date and time values work in spreadsheets.

Upvotes: 1

BigBenne
BigBenne

Reputation: 117

So I just found a more efficiency way to do what I wanted.

I just remove the second for loop, it was useless, so I

s.getRange(i-2,col).setValue(newVal[i]); into the first for loop.

Upvotes: 0

Related Questions