在Google表格＃2的日期范围内每天添加一行并复制原始行中的信息 [英] Adding a row and copying the information from the original row for every day in a date range in Google Sheets #2

问题描述

我拥有Google表格，Google表格中的信息被转储。其中两列创建一个日期范围（C列和G列），我希望工作表为每个范围的日期自动创建一行新的信息，并复制原始行中的所有其他信息创建。最后，范围中的每个日期都有它自己的行，不管它是2天还是25天，并且通过表单收集的所有信息每天都会出现。如果G列中没有日期，则只有一天的行程，并且不需要额外的行。当有人提交表单时，为了让事情变得更加困难，信息将被输入到最后一行填充的行的正下方，因此这些由日期范围填充的新行将需要放在表单中，可能从行2000开始，或者更多的是因为这张表在几个月内会有很多信息。正如您在示例中看到的那样，工作簿中还有另一张表单执行所有排序。谢谢你的帮助。

和之后：

现在，您可以将表单单独链接到表单并让它成为提交数据的档案。

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

解决方案

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.

这篇关于在Google表格＃2的日期范围内每天添加一行并复制原始行中的信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！