CODEWITHSUNDEEP
google-apps-script
bp123
bp123

Reputation: 3417

Google script keeps crashing due to large data set

I'm using google scripts to access a google sheet to present the data in a visual manner. The function below works, however, it's starting to crash or run slow due to the amount of data in the google sheet. Google is throwing a warning Method Range.getValue is heavily used by the script..

How am I suppose to write this? 

function generateUsers() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rr = spreadSheet.getLastRow();

  var users = [];
  for (var i = 3; i <= rr; i++) {
    var firstName = spreadSheet.getRange(i, 1).getValue();
    var lastName = spreadSheet.getRange(i, 2).getValue();
    var phone = spreadSheet.getRange(i, 12).getValue();

    var user = {
      firstName: firstName,
      lastName: lastName,
      phone: phone
    };

    users.push(user);
  }
  return users; // Modified
}

Upvotes: 2

Views: 579

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about this modification? Please think of this as just one of several answers.

Modified script:

In this modification, at first, all values are retrieved from the Spreadsheet. And then, user is created from the retrieved values. Please modify your script as follows.

From: 
var users = [];
for (var i = 3; i <= rr; i++) {
  var firstName = spreadSheet.getRange(i, 1).getValue();
  var lastName = spreadSheet.getRange(i, 2).getValue();
  var phone = spreadSheet.getRange(i, 12).getValue();

  var user = {
    firstName: firstName,
    lastName: lastName,
    phone: phone
  };

  users.push(user);
}
To: 
var values = spreadSheet.getRange(3, 1, rr - 2, 12).getValues();
var users = values.map(function(row) {
  return {
    firstName: row[0],
    lastName: row[1],
    phone: row[11]
  };
});

References:

If this was not the result you want, I apologize.

Upvotes: 3

Related Questions