CODEWITHSUNDEEP
androidjsongoogle-apps-scriptgoogle-sheets
seba123neo
seba123neo

Reputation: 4748

Extremely slow to get JSON with Apps Script Spreadsheet

I have an Excel workbook (Spreadsheet) in my drive account with multiple sheets, in one of those sheets I have 5000 records.

I'm using this script (Apps Script) to get the records in JSON format, from a android device: The script works if the records are few, but with a lot of records never finish.

    function traerRubros() {
  var libro = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1-Vv3tsn7QYDa7rOsha4ebj9vJyjegG7j-xpDMbOOAuY/edit#gid=825509855');

  var sheet = libro.getSheetByName('Articulos');

  var json1 = convertSheet2JsonText(sheet);

  return JSON.stringify(json1);
}

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

Why does it take so long? Is there any optimization on this script that you can do to make it faster? What alternative I can recommend to obtain records quickly?

Upvotes: 1

Views: 527

Answers (1)

Spencer Easton
Spencer Easton

Reputation: 5782

Doing a getRange() or getValues() inside a loop will always make your code slow. Secondly getValues() returns Array[][]. There is no need to copy it into another array. 

 // after the second line(data)
  var lastRow = sheet.getLastRow();
  var colStartIndex = 1;
  var rowStart = 2; // Column and Rows start at index 1
  var rowValues = sheet.getRange(rowStart, colStartIndex, sheet.getLastRow(),    sheet.getLastColumn()).getValues();

// create json
  var jsonArray = [];
...
...

Upvotes: 2

Related Questions