Unable to process the operation because it contain

Question

I have two sheets. First is data from JSON with max results 100 rows. It contains 8 columns.

Second is the data I add manually and then write to the first sheet based on matched title. For example, if both titles match then create a new column "Category" in first sheet from second sheet. The second sheet contains 50 rows and 8 columns.

When I run this script it throws error: We're sorry, we were unable to process the operation because it contains too much data. I tried to remove line by line to figure out what is causing it. And it seems like when I remove this line:

data[i][11] = descr; // this is a paragraph long description text

It is working fine. Also, if I remove all the other data I want to write in, and run only data[i][11] = descr; it also chokes. So, it doesn' seem like there is too much data. Any ideas how to make it work? Workarounds?

Edit: here is a copy of the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AhVWrVLsk5a5dFRaeFQxZUc3WlZOR0h4N09pOGJBdGc&usp=sharing

Thanks!

function listClasses(){

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0]; //list of all upcoming classes
 var sheet1 = ss.getSheets()[1]; //list of titles

 var data = sheet.getDataRange().getValues(); // read all data in the sheet
 var data1 = sheet1.getDataRange().getValues();

   for(n=1;n<data1.length;n++){
    var title1 = data1[n][0];
    var category = data1[n][1];
    var image_url = data1[n][2];
    var available = data1[n][3];
    var descr = data1[n][4];
    var prerequisites = data1[n][5];
    var mAccess = data1[n][6];
    var notes = data1[n][7];

  // compare Check if title appears in column B of sheet 1.
  if (ArrayLib.find(data1, 0, title1) != -1) {
    // Yes it does, do something in sheet 0
    for( var i = data.length -1; i >= 0; --i )
      if (data[i][1] == title1) 
      { 

        //Logger.log(descr);

      if (data[i].length < 14){

          data[i].push(' ');

        }

          data[i][8] = category;
          data[i][9] = image_url;
          data[i][10] = available;
          data[i][11] = descr;
          data[i][12] = prerequisites;
          data[i][13] = mAccess;
          data[i][14] = notes;


        sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

      }

          }

      }

Answer 1

I had a look at your code, but I could not make it work.

I too get such error messages, but so far have encountered them when I am processing a few thousand rows (with only 4 columns) but each row containing as much text or more than your description.

I only know how to use a simple way, which I tried for your case too. The following code I think does what you need:

function listClasses2(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; //list of all upcoming classes
  var sheet1 = ss.getSheets()[1]; //list of titles

  var data = sheet.getDataRange().getValues(); // read all data in the sheet
  var data1 = sheet1.getDataRange().getValues();

  var newDataArray = [data[0]];

  for(var n=1; n < data.length ; n++){
    for(var j=0; j< data1.length ; j++){
      if(data[n][1] == data1[j][0]){
        newDataArray.push([data[n][0] ,data[n][1] ,data[n][2] ,data[n][3] ,data[n][4] ,data[n][5] ,data[n][6] ,data[n][7] ,data1[j][1] , data1[j][2] , data1[j][3] , data1[j][4] , data1[j][5] , data1[j][6], data1[j][7] ]) ;
        break;}    
    }   
    newDataArray.push(data[n])
  }
  sheet.getRange(1, 1, newDataArray.length, newDataArray[0].length).setValues(newDataArray);
}

(I have just noticed that my var n is not the same as your var n ... sorry for the possible confusion)