title: Complex transpose exceeding run time in Google Apps Script
body: <p>I am receiving data in a single column and must transpose that into individual records. Some records will be 12 characters long, others 10, and the remainder 9. Furthermore, the latter 2 values in the 10 and 9-character-long records must be shifted 1 and 2 fields to the right, respectively. The first value in a given record is always a date. I have created the following code which works well, except that it times out after about 6 minutes and 77 records. I need to be able to handle 15 times as many if not more.</p>
  
  <p>I embedded the calculation of the date objects in the else section of each if statement and nested the subsequent if statements in an effort to reduce unnecessary calculations. This got me from about 48 records to 77.</p>
  
  <p>Very grateful for any clever insight </p>
  
  <hr>
  
  <pre><code>function transposeNew(){
    let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    let lr = ss.getRange("A13").getDataRegion().getLastRow();
    let sr = 13
  
    // get the data column
    let data = ss.getRange(sr,1,lr-sr,1).getValues();
  
    // set up the rows loop
    let pasteRow = 2;
    let arrayField = 0;
    while (arrayField &lt; data.length){
      //use the new Date() constructor to create a date object with the date value passed
      let isDate12 = new Date(data[arrayField+12]).getFullYear(); //processed; size input;record should include 12 rows &amp; 13th should be a date to begin the next row
      if (isDate12 === 2020) {
        let record = data.slice(arrayField, arrayField+12);
        let recordTr = transposeSub(record);
        ss.getRange(pasteRow, 5, 1, 12).setValues(recordTr);
        arrayField = arrayField + 12;
      }
      else {
        let isDate10 = new Date(data[arrayField+10]).getFullYear(); //unprocessed;size input
        if (isDate10 === 2020) {
          let record = data.slice(arrayField, arrayField+10);
          let record1 = record.slice(0,8);
          let record1Tr = transposeSub(record1);
          let record2 = record.slice(8,10);
          let record2Tr = transposeSub(record2);
          ss.getRange(pasteRow, 5, 1, 8).setValues(record1Tr);
          ss.getRange(pasteRow, 14, 1, 2).setValues(record2Tr);
          arrayField = arrayField + 10;
        }
        else {
          let isDate9 = new Date(data[arrayField+9]).getFullYear(); //unprocessed;no size 
            input
          if (isDate9 === 2020) {
            let record = data.slice(arrayField, arrayField+10);
            let record1 = record.slice(0,7);
            let record1Tr = transposeSub(record1);
            let record2 = record.slice(7,9);
            let record2Tr = transposeSub(record2);
            ss.getRange(pasteRow, 5, 1, 7).setValues(record1Tr);
            ss.getRange(pasteRow, 14, 1, 2).setValues(record2Tr);
            arrayField = arrayField + 9;
          }
        }
      }
      pasteRow ++;
    }
  }
  
  function transposeSub(a)
  {
    return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
  }
  </code></pre>