Reading from one tab in google sheet and writing to another sheet with some modifications.Any way to improve speed to avoid execution time out error?

Question

I have tab called 'site base production plan' in a google sheet where there are two tables. In the first table each row has information about a crop. For example

Picking means harvesting and frequency is basically harvest after every 4 days in the current example

Now in the second table there is a breakup of this record where there are multiple rows corresponding to this row. For example

Now basis this data I want to create an output data set for harvest schedule. For example

Please note that there are additional columns but I have just included the most relevant ones in the example.

The link to the google sheet is as below

https://docs.google.com/spreadsheets/d/1mLBELSyyB1Ou5NjZ4uOv9GzNygz8-qLlkUcgxjFEjz8/edit?usp=sharing

Now, I am not from coding background, but did learn c++ in college (and now I middle aged !).I tried a very C++ ish approach which works just fine except the that its too slow (since I am reading and writing each cell and which is slow as per a lot of articles an youtube tutorials). I did go through a lot of tutorials to write this code, but then because of slowness I tried using arrays. The code with arrays doesn't seem to get the intended output. At this point if someone could just help write the array based code I would be highly appreciate it. The code that I wrote is as below

    function writetoSheet()
    {
    var app = SpreadsheetApp;
    var ss = app.getActiveSpreadsheet();
    var tab = ss.getActiveSheet();
    var write_tab = ss.getSheetByName("Expected Site Production Data");
    var read_tab = ss.getSheetByName("Site base production plan");
    var lstrowtable1 = columnlastrow(read_tab,1);                        //Gets last row of table 
    var lstrowtable2 = columnlastrow(read_tab,23);                       //Gets last row of table 
    generateharvestdata(lstrowtable1,lstrowtable2,read_tab,write_tab);
    }
    
    function columnlastrow(read_tab,clmn)                      
    {
      var j= read_tab.getLastRow()+1;
      do {      
          j=j-1;
        } while(read_tab.getRange(j,clmn).getValue() == "")
      return j;
    }
    
    function generateharvestdata (lstrowtable1,lstrowtable2,read_tab,write_tab)
{
 var k=2
 for ( var i =1;i <= lstrowtable1;i++)
 {  
   var checkvalidaggregate = read_tab.getRange(i+2,22).getValue();
   if(checkvalidaggregate != "N")
   {
    var recordid1=read_tab.getRange(i+2,1).getValue();
      for ( var j=1; j<=lstrowtable2;j++)
      { 
        var recordid2 = read_tab.getRange(j+2,23).getValue();
        var checkvalidbreakup = read_tab.getRange(j+2,43).getValue();
        if(recordid1 == recordid2 && checkvalidbreakup != "N" ) 
         {  
          var frequency=read_tab.getRange(j+2,34).getValue();
          var pickingsdate=new Date(read_tab.getRange(j+2,35).getValue());
          var pickingedate=new Date(read_tab.getRange(j+2,36).getValue());
          var currentpickingdate= new Date();
          currentpickingdate = pickingsdate;
          do
            {
              write_tab.getRange(k,1).setValue(recordid2);          
                
              var siteid= read_tab.getRange(j+2,24).getValue();    
              write_tab.getRange(k,2).setValue(siteid);            
                
              var fy_year= read_tab.getRange(j+2,25).getValue();
              write_tab.getRange(k,3).setValue(fy_year);
                
              var crop=read_tab.getRange(j+2,26).getValue();
              write_tab.getRange(k,4).setValue(crop);

              var season=read_tab.getRange(j+2,27).getValue();
              write_tab.getRange(k,5).setValue(season);
                
              write_tab.getRange(k,6).setValue(currentpickingdate);
               
              var pickquanity=read_tab.getRange(j+2,37).getValue();
              write_tab.getRange(k,7).setValue(pickquanity);
               
              //var unitofmeasure=read_tab.getRange(j+2,32).getValue();
              //write_tab.getRange(k,8).setValue(unitofmeasure);
                
              currentpickingdate.setTime(currentpickingdate.getTime()+frequency*(24*60*60*1000));
                
              k++;
          
              } while(currentpickingdate.getTime()< pickingedate.getTime())
          }
      }    
   } 
 }
}

**// Attempt using arrays...**

    /*function generateharvestdata (lstrowtable1,lstrowtable2,read_tab,write_tab)
{ var harvestdarray =[];
  harvestdarray = [  
                    ["Base Production Record ID","Site ID","Financial Year","Crop","Season","Picking date","Picking Quantity","Unit of measurement"]
                  ];
 for ( var i =1;i <= lstrowtable1;i++)
 {  
   var checkvalidaggregate = read_tab.getRange(i+2,22).getValue();
   if(checkvalidaggregate != "N")
   {
    var recordid1=read_tab.getRange(i+2,1).getValue();
      for ( var j=1; j<=lstrowtable2;j++)
      { 
        var recordid2 = read_tab.getRange(j+2,23).getValue();
        var checkvalidbreakup = read_tab.getRange(j+2,43).getValue();
        if(recordid1 == recordid2 && checkvalidbreakup != "N" ) 
         {  
          var frequency = read_tab.getRange(j+2,34).getValue();
          var pickingsdate=new Date(read_tab.getRange(j+2,35).getValue());
          var pickingedate=new Date(read_tab.getRange(j+2,36).getValue());
          var currentpickingdate= new Date();
          currentpickingdate = pickingsdate;
          do
            {
              var siteid = read_tab.getRange(j+2,24).getValue();
              var fy_year = read_tab.getRange(j+2,25).getValue();
              var crop=read_tab.getRange(j+2,23).getValue();
              var cropping_season =read_tab.getRange(j+2,26).getValue();
              var pickingquantity = read_tab.getRange(j+2,27).getValue();
              var unitofmeasure = read_tab.getRange(j+2,37).getValue();
              
              harvestdarray.push([recordid2,siteid,fy_year,crop,cropping_season,currentpickingdate,pickingquantity,unitofmeasure]);
              currentpickingdate.setTime(currentpickingdate.getTime()+frequency*(24*60*60*1000));
                
              } while(currentpickingdate.getTime()< pickingedate.getTime())
            write_tab.getRange(1,1,harvestdarray.length,8).setValues(harvestdarray);
          }
      }    
   } 
 }
}
*/

Answer 1

I could not figure out what you were trying to do by reading you code so I started with the following array:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
1	2	3	4	5	6	7	8	9	10
2	3	4	5	6	7	8	9	10	11
3	4	5	6	7	8	9	10	11	12
4	5	6	7	8	9	10	11	12	13
5	6	7	8	9	10	11	12	13	14
6	7	8	9	10	11	12	13	14	15
7	8	9	10	11	12	13	14	15	16
8	9	10	11	12	13	14	15	16	17
9	10	11	12	13	14	15	16	17	18
10	11	12	13	14	15	16	17	18	19

I decided to return only even rows and even columns where COL10 > 15

Here's the code:

function readFrom1copyevensto2ifcolumn10isgreaterthan15() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet1");
  const sh2 = ss.getSheetByName("Sheet2");
  sh2.clearContents();
  const vs = sh1.getRange(2, 1, sh1.getLastRow() - 1, sh1.getLastColumn()).getValues().filter((r, i) => (i % 2 == 1 && r[9] > 15)).map(r => [r[1],r[3],r[5],r[7],r[9]]);//I just picked the even columns manually

  if(vs && vs.length > 0) {
    vs.unshift(sh1.getRange(1,1,1,sh1.getLastColumn()).getValues().map(r => [r[1],r[3],r[5],r[7],r[9]]).flat());//puts titles of of even columns back into the array again by picking them manually
    sh2.getRange(1,1,vs.length,vs[0].length).setValues(vs);
  }
}
//returns only even columns and even rows where column 10 > 15

Here's the output:

COL2	COL4	COL6	COL8	COL10
9	11	13	15	17
11	13	15	17	19

Hopefully this example will assist you in figuring out how to get your desired output.