title: Google apps script spreadsheet:error
body: <p>I work for a public school district and have written a script to take data from a Google form response sheet and filter it into separate sheets based on which school the information relates to. Using the tutorial located at <a href="https://developers.google.com/apps-script/guides/sheets" rel="nofollow">https://developers.google.com/apps-script/guides/sheets</a> . The modified script is 1300 lines long (including some explinations) and has been running smoothly up until recently.</p>
  
  <p>The script now returns numerous errors. Most commonly I see errors such as:</p>
  
  <p>"Service timed out: Spreadsheets (line 40, file "Transfer/SortID/Copy/Sort")"</p>
  
  <p>"Service error: Spreadsheets (line 40, file "Transfer/SortID/Copy/Sort")"</p>
  
  <p>The line that is flagged in the error is </p>
  
  <p>"headersRange.setValues([columnNames]);"</p>
  
  <p>I have tried everything that I can think of including creating a new target spreadsheet.</p>
  
  <p>I have included the beginning section of the code below. Again, this script was running great up until recently. (Last time it ran successfully was 5/6)</p>
  
  <p>Thanks in advance for your help!</p>
  
  <pre><code>// This is where the data used in this example will be retrieved from:
  // https://docs.google.com/a/psdschools.org/spreadsheet/ccc?key=0AswYUpTPhetrdFYxUEJZSkxHeVdfSk5pajh3UjYxaUE&amp;usp=drive_web#gid=0
  function TransferAndSort() {{
  var DATA_SPREADSHEET_ID = "0AswYUpTPhetrdFYxUEJZSkxHeVdfSk5pajh3UjYxaUE"
  
  //update data
  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
  
    var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
    var dataSheet = dataSs.getSheets()[0];
  
    // Fetch all the data
    var data = getRowsData(dataSheet);
  
    // This is the data we want to display
    var columnNames = ["Last Name","First Name", "ID Number", "School", "Date Written", "Purpose", "Requested Route #", "Requested Stop Location", "Type of Student", "Contact Made"];
  
  
  // Index data by School name
    var dataBySchool = {};
    var schools = [];
    for (var i = 0; i &lt; data.length; ++i) {
      var rowData = data[i];
      if (!dataBySchool[rowData.school]) {
        dataBySchool[rowData.school] = [];
        schools.push(rowData.school);
      }
      dataBySchool[rowData.school].push(rowData);
    }
  
    schools.sort();
    var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
    for (var i = 0; i &lt; schools.length; ++i) {
      var sheet = ss.getSheetByName(schools[i]) ||
        ss.insertSheet(schools[i], ss.getSheets().length);
      sheet.clear();
      var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
      headersRange.setValues([columnNames]);
      headersRange.setBackgroundColor(headerBackgroundColor);
      setRowsData(sheet, dataBySchool[schools[i]]);
    }
  }
  
  // setRowsData fills in one row of data per object defined in the objects Array.
  // For every Column, it checks if data objects define a value for it.
  // Arguments:
  //   - sheet: the Sheet Object where the data will be written
  //   - objects: an Array of Objects, each of which contains data for a row
  //   - optHeadersRange: a Range of cells where the column headers are defined. This
  //     defaults to the entire first row in sheet.
  //   - optFirstDataRowIndex: index of the first row where data should be written. This
  //     defaults to the row immediately below the headers.
  function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
    var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
    var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
    var headers = normalizeHeaders(headersRange.getValues()[0]);
  
    var data = [];
    for (var i = 0; i &lt; objects.length; ++i) {
      var values = []
      for (j = 0; j &lt; headers.length; ++j) {
        var header = headers[j];
        // If the header is non-empty and the object value is 0...
        if ((header.length &gt; 0) &amp;&amp; (objects[i][header] == 0)) {
          values.push(0);
        }
        // If the header is non-empty or the object value is empty...
        else if ((!(header.length &gt; 0)) || (objects[i][header]=='')) {
          values.push('');
        }
        else {
          values.push(objects[i][header]);
        }
      }
      data.push(values);
    }
  
    var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(),
                                          objects.length, headers.length);
    destinationRange.setValues(data);
  }
  
  // getRowsData iterates row by row in the input range and returns an array of objects.
  // Each object contains all the data for a given row, indexed by its normalized column name.
  // Arguments:
  //   - sheet: the sheet object that contains the data to be processed
  //   - range: the exact range of cells where the data is stored
  //       This argument is optional and it defaults to all the cells except those in the first row
  //       or all the cells below columnHeadersRowIndex (if defined).
  //   - columnHeadersRowIndex: specifies the row number where the column names are stored.
  //       This argument is optional and it defaults to the row immediately above range;
  // Returns an Array of objects.
  function getRowsData(sheet, range, columnHeadersRowIndex) {
    var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
    var dataRange = range ||
      sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
    var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
    var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
    var headers = headersRange.getValues()[0];
    return getObjects(dataRange.getValues(), normalizeHeaders(headers));
  }
  
  // For every row of data in data, generates an object that contains the data. Names of
  // object fields are defined in keys.
  // Arguments:
  //   - data: JavaScript 2d array
  //   - keys: Array of Strings that define the property names for the objects to create
  function getObjects(data, keys) {
    var objects = [];
    for (var i = 0; i &lt; data.length; ++i) {
      var object = {};
      var hasData = false;
      for (var j = 0; j &lt; data[i].length; ++j) {
        var cellData = data[i][j];
        if (isCellEmpty(cellData)) {
          continue;
        }
        object[keys[j]] = cellData;
        hasData = true;
      }
      if (hasData) {
        objects.push(object);
      }
    }
    return objects;
  }
  
  // Returns an Array of normalized Strings.
  // Empty Strings are returned for all Strings that could not be successfully normalized.
  // Arguments:
  //   - headers: Array of Strings to normalize
  function normalizeHeaders(headers) {
    var keys = [];
    for (var i = 0; i &lt; headers.length; ++i) {
      keys.push(normalizeHeader(headers[i]));
    }
    return keys;
  }
  
  // Normalizes a string, by removing all alphanumeric characters and using mixed case
  // to separate words. The output will always start with a lower case letter.
  // This function is designed to produce JavaScript object property names.
  // Arguments:
  //   - header: string to normalize
  // Examples:
  //   "First Name" -&gt; "firstName"
  //   "Market Cap (millions) -&gt; "marketCapMillions
  //   "1 number at the beginning is ignored" -&gt; "numberAtTheBeginningIsIgnored"
  function normalizeHeader(header) {
    var key = "";
    var upperCase = false;
    for (var i = 0; i &lt; header.length; ++i) {
      var letter = header[i];
      if (letter == " " &amp;&amp; key.length &gt; 0) {
        upperCase = true;
        continue;
      }
      if (!isAlnum(letter)) {
        continue;
      }
      if (key.length == 0 &amp;&amp; isDigit(letter)) {
        continue; // first character must be a letter
      }
      if (upperCase) {
        upperCase = false;
        key += letter.toUpperCase();
      } else {
        key += letter.toLowerCase();
      }
    }
    return key;
  }
  
  // Returns true if the cell where cellData was read from is empty.
  // Arguments:
  //   - cellData: string
  function isCellEmpty(cellData) {
    return typeof(cellData) == "string" &amp;&amp; cellData == "";
  }
  
  // Returns true if the character char is alphabetical, false otherwise.
  function isAlnum(char) {
    return char &gt;= 'A' &amp;&amp; char &lt;= 'Z' ||
      char &gt;= 'a' &amp;&amp; char &lt;= 'z' ||
      isDigit(char);
  }
  
  // Returns true if the character char is a digit, false otherwise.
  function isDigit(char) {
    return char &gt;= '0' &amp;&amp; char &lt;= '9';
  }
  </code></pre>