Adding a row and copying the information from the original row for every day in a date range in Google Sheets -
i have google sheet information google form dumped. 2 of columns create date range , sheet automatically create new row of information every date of range , copy other information original row every row created. in end, every date in range has it's own row regardless of being 2 days or 25 , the information gathered through form present each day
here's function trick you. spreadsheet below after running. append bottom of starting data.
function convertdaterangetorows() { var ss=spreadsheetapp.getactive(); var sht=ss.getactivesheet(); 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][0] && rnga[i][1] && rnga[i][0]!=rnga[i][1]) { var dt0=new date(rnga[i][0]); var dt1=new date(rnga[i][1]); var days=(dt1.valueof()-dt0.valueof())/day; var dt=dt0.valueof(); for(j=0;j<days;j++) { dt+=day; rngb.push([utilities.formatdate(new date(dt), session.getscripttimezone(), "mm/dd/yyyy"),'',rnga[i][2],rnga[i][3],rnga[i][4]]); } } var intermediate='nothing'; } var rngb=sht.getrange(sht.getlastrow()+1,1,rngb.length,rngb[0].length); rngb.setvalues(rngb); var end='the end near'; }
Comments
Post a Comment