Google scripts to copy and repeat row

Question

Hi all I'm no good with scripts at all, just need some help, what I want to do is copy and repeat cells a,b,c,d,e, based on info in cells F and G.

In cells d and e are dates and they need to increase by value in cell F and keep repeating based on value in G, I have made a sample sheet, on the sheet input is the data, and on sheet output it shows the results I would need. But all info needs to stay on the input sheet not a different sheet. Hope u can understand what I mean when u see the sample sheet. On the sheet in cell f it says weeks, this would be better in days then weeks.

Title   Description     Location        Start Time      End Time     repeate every  how many times to repeat                                                                            
test    tester          here            9/7/17 20:00    9/7/17 20:10    2 weeks     4                                                                           
fake    data            home            15/7/17 15:00   15/7/17 16:00   4 weeks     3

original Google sheet

The overall goal I am trying to get is to be able to add reoccurring events to Google calendar with scripts automatically when submitted with a form, but if I can repeat the data I need in the sheets I can then sync it to the calendar automatically and then I can set the reoccurring events based on days as I believe the reoccurring event for calendar only lets u do daily weekly monthly..

Or Can anyone point me to a site where I may find some info to help me please?

Can anyone point me to a site where I may find some info to help me please? — Ditch1983

Cooper Cooper · Accepted Answer · 2017-07-15T17:42:05

I changed the repeat to days instead of weeks.

function repeatingEvents() 
{
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sht=ss.getSheetByName('Repeat');
    var rng=sht.getDataRange();//Get all of the data on the sheet
    var rngA=rng.getValues();
    var day=86400000;//milliseconds in a day
    var week=604800000;//millisecond in a week
    for(var i=0;i<rngA.length;i++)
    {
      if(rngA[i][6]>0)//If the number of repeats is greater than zero
      {
        var cnt=Number(rngA[i][6]);//cnt is number of repeats
        for(var n=0;n<cnt;n++)
        {
          var row=[];
          row.push(rngA[i][0]);//column A
          row.push(rngA[i][1]);//column B
          row.push(rngA[i][2]);//column C
          row.push(new Date(rngA[i][3].getTime() + (rngA[i][5] * (n + 1) * day)));//old date time plus repeat duration * repeat count * milliseconds in a day in column D
          row.push(new Date(rngA[i][4].getTime() + (rngA[i][5] * (n + 1) * day)));//column E
          row.push(rngA[i][5]);//Column F
          row.push('');//column G get zeroed out so script won't come back and do it again without human interaction
          sht.appendRow(row);//add new event
         }
         sht.getRange(i+1,7).setValue(0);//zero out initial number of repeats because I use this to determine when repeats are required
      }
    }
    sht.getRange(2,1,sht.getLastRow(),sht.getLastColumn()).sort({column:4,ascending:true});//Sort Spreadsheet by starting date
}

Here's what my spreadsheet looks like.

Okay I changed the code to accomodate your recent change of adding the id. It's already in your spreadsheet and I repeat it here. For reference purposes.

function repeatingEvents() 
{
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sht=ss.getSheetByName('input');
    var rng=sht.getDataRange();//Get all of the data on the sheet
    var rngA=rng.getValues();
    var day=86400000;//milliseconds in a day
    var week=604800000;//millisecond in a week
    for(var i=0;i<rngA.length;i++)
    {
      if(rngA[i][7]>0)//If the number of repeats is greater than zero
      {
        var cnt=Number(rngA[i][7]);//cnt is number of repeats
        for(var n=0;n<cnt;n++)
        {
          var row=[];
          row.push(rngA[i][0]);//A
          row.push(rngA[i][1]);//B
          row.push(rngA[i][2]);//C
          row.push(new Date(rngA[i][3].getTime() + (rngA[i][6] * (n + 1) * day)));//old date time plus repeat duration * repeat count * milliseconds in a day D
          row.push(new Date(rngA[i][4].getTime() + (rngA[i][6] * (n + 1) * day)));//E
          row.push(rngA[i][5]);//F
          row.push(rngA[i][6]);//G
          row.push('');//H
          sht.appendRow(row);//add new event
         }
         sht.getRange(i+1,8).setValue(0);//zero out initial number of repeats because I use this to determine when repeats are required
      }
    }
    sht.getRange(2,1,sht.getLastRow(),sht.getLastColumn()).sort({column:4,ascending:true});//Sort Spreadsheet by starting date
}

Google scripts to copy and repeat row

2 Answers