Adding a row and copying the information from the original row for every day in a date range in Google Sheets #2

Question

I have a Google Sheet where information from a Google Form is dumped. Two of the columns create a date range (columns C and G) and I would like for the sheet to automatically create a new row of information for every date of the range and copy all the other information from the original row for every row that is created. In the end, every date in the range has it's own row regardless of it being 2 days or 25 and all the the information gathered through the form be present for each day. If there is not a date in column G, it is only a one day trip and there is no need for additional rows. To make things more difficult when someone submits a form, the information is entered into the row directly beneath the last one that it filled, so these new rows filled by the date range will need to be down the sheet, possibly beginning at row 2000 or more as this sheet will have a lot of information in a few months. As you may see in the sample, there is another sheet in the workbook that performs all the sorting. Thanks for any help.

Sample Document

Answer 1

You will need to create a form submit event and attach the following code to it. Also you'll need to create a sheet name 'ResponseReview'.

function formSubmitEvent1(e) 
{
    var ss=SpreadsheetApp.openById('SpreadsheetID');
    var sht=ss.getSheetByName('ResponseReview');
    sht.appendRow(e.values);
}

The above code will need the SpreadsheetId in the openById Method. This code will append any new rows to the end of the ResponseReview sheet.

The code below will expand any entrees that have a date in column 3 and 7 and it will also remove the end date in column 7 from that first row. I use the fact that if column 3 is not empty and column 7 is not and column 7 is no equal to column 3 then that's a row that needs to be expanded. So I have to remove the end date so that it won't continue to get expanded when it's run again in the future. We could figure something else out if you need to keep than end date. We could add a don't expand column at the end.

function convertRangetoRows()
{
  var ss=SpreadsheetApp.getActive();
  var sht=ss.getSheetByName('ResponseReview');
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  var rngB=[];
  var day=86400000;
  rngB.push(rngA[0]);
  for(var i=1;i<rngA.length;i++)
  {
    rngB.push(rngA[i]);  
    if(rngA[i][2] && rngA[i][6] && rngA[i][2]!=rngA[i][6])
    {
      var row=rngA[i].slice();//returns a new copy of the array by value
      rngA[i][6]='';//deletes the end date by reference so it also deletes the one thats already been pushed into rngB
      var dt0=new Date(row[2]);
      var dt1=new Date(row[6]);
      var days=(dt1.valueOf()-dt0.valueOf())/day;
      var dt=dt0.valueOf();
      for(j=0;j<days;j++)
      {
        dt+=day;
        row[2]=Utilities.formatDate(new Date(dt), Session.getScriptTimeZone(), "MM/dd/yyyy");//original array unchanged
        row[6]='';//original array unchanged
        rngB.push(row.slice());//push in a copy
      }
    }
   var intermediate='nothing'; 
  }
  var outrng=sht.getRange(1,1,rngB.length,rngB[0].length);
  outrng.setValues(rngB);
  var end='the end is near';
}

This is what my spreadsheet looks like before running the expansion function:

And After:

And now you can leave the sheet linked to the form alone and let it be an archive for submitted data.