google apps script - How to set values for all columns except one? -
my code works great, take events spreadsheet , creates them on google calendar. however, 1 of columns in spreadsheet contains formula. everytime run code, formula disappears , replaced whatever on cell @ time.
i know issue is:
// record event ids spreadsheet except row 7 range.setvalues(data); how can write loop apply row[0] row[8] skipping row [7]?
but here full code reference:
function onopen() { var sheet = spreadsheetapp.getactivespreadsheet(); var entries = [{ name : "export events", functionname : "exportevents" }]; sheet.addmenu("update calendar", entries); } //export events spreadsheet calendar// function exportevents() { var sheet = spreadsheetapp.getactivesheet(); var headerrows = 1; // number of rows of header info (to skip) var range = sheet.getdatarange(); var data = range.getvalues(); var calid = "calendar_id"; var cal = calendarapp.getcalendarbyid(calid); (i in data) { if (i < headerrows) continue; // skip header row(s) var row = data[i]; var date = new date(row[0]); // first column var title = row[1]; // second column var tstart = new date(row[2]); tstart.setdate(date.getdate()); tstart.setmonth(date.getmonth()); tstart.setyear(date.getyear()); var tstop = new date(row[3]); tstop.setdate(date.getdate()); tstop.setmonth(date.getmonth()); tstop.setyear(date.getyear()); var loc = row[4]; var desc = row[5]; var complete = row[6]; var status = row[7]; var id = row[8]; // 8 column == eventid // check if event exists, update if try { var event = cal.geteventseriesbyid(id); } catch (e) { // nothing - want avoid exception when event doesn't exist } if (!event) { //cal.createevent(title, new date("march 3, 2010 08:00:00"), new date("march 3, 2010 09:00:00"), {description:desc,location:loc}); var newevent = cal.createevent(title, tstart, tstop, {description:"("+status+") "+desc,location:loc}).getid(); row[8] = newevent; // update data array event id } else { event.settitle(title); event.setdescription("("+status+") "+desc); event.setlocation(loc); // event.settime(tstart, tstop); // cannot settime on eventseries. // ... can set recurrence! var recurrence = calendarapp.newrecurrence().adddailyrule().times(1); event.setrecurrence(recurrence, tstart, tstop); } debugger; } // record event ids spreadsheet except row 7 range.setvalues(data); }
there no way write "selectively" using setvalues... suggest splice array (vertically) in 2 new arrays , write 2 different setvalues, leaving formula's column untouched.
note splice array method splices horizontally (for 2d array) you'll have loop first level , splicing each row working arrays fast won't issue.
example code :(i took data sheet demonstrate)
function splicevertically() { var data = spreadsheetapp.getactive().getactivesheet().getdatarange().getvalues(); logger.log('data = '+json.stringify(data)); var data1 = []; var data2 = []; for(var n in data){ data2.push(data[n].splice(7,1));// cut row @ col 8 , keep 1 data1.push(data[n].splice(0,6));// cut @ 0 , keep 6 , method cut array >> data2 before data1 } logger.log('data1 = '+json.stringify(data1)); logger.log('data2 = '+json.stringify(data2)); } 
edit :
to insert in code use :
sh.getrange(1,1,data1.length,data1[0].length).setvalues(data1);// update col 1 6 sh.getrange(1,8,data2.length,data2[0].length).setvalues(data2);// update col 8 btw, can use slice method ... below complete test sheet update :
function splicevertically() { var sh = spreadsheetapp.getactive().getactivesheet(); var range = sh.getdatarange(); var data = range.getvalues(); logger.log('data = '+json.stringify(data)); var data1 = []; var data2 = []; for(var n in data){ data1.push(data[n].slice(0,8)); data2.push(data[n].slice(9,10)); } logger.log('data1 = '+json.stringify(data1)); logger.log('data2 = '+json.stringify(data2)); sh.getrange(1,1,data1.length,data1[0].length).setvalues(data1); sh.getrange(1,10,data2.length,data2[0].length).setvalues(data2); } 
final edit : full code implemented in yours
since seems didn't working, below full implementation. tested on sheet
function onopen() { var sheet = spreadsheetapp.getactivespreadsheet(); var entries = [{ name : "export events", functionname : "exportevents" }]; sheet.addmenu("update calendar", entries); } //export events spreadsheet calendar// function exportevents() { var sheet = spreadsheetapp.getactivesheet(); var headerrows = 1; // number of rows of header info (to skip) var range = sheet.getdatarange(); var data = range.getvalues(); var calid = "h22nevo15tm0nojb6ul4hu7ft8@group.calendar.google.com"; //removed link on purpose var cal = calendarapp.getcalendarbyid(calid); (i in data) { if (i < headerrows) continue; // skip header row(s) var row = data[i]; var date = new date(row[0]); // first column var title = row[1]; // second column var tstart = new date(row[2]); tstart.setdate(date.getdate()); tstart.setmonth(date.getmonth()); tstart.setyear(date.getyear()); var tstop = new date(row[3]); tstop.setdate(date.getdate()); tstop.setmonth(date.getmonth()); tstop.setyear(date.getyear()); var loc = row[4]; var desc = row[5]; var complete = row[6]; var status = row[7]; var id = row[8]; // 8 column == eventid // check if event exists, update if try { var event = cal.geteventseriesbyid(id); } catch (e) { // nothing - want avoid exception when event doesn't exist } if (!event) { //cal.createevent(title, new date("march 3, 2010 08:00:00"), new date("march 3, 2010 09:00:00"), {description:desc,location:loc}); var newevent = cal.createevent(title, tstart, tstop, {description:"("+status+") "+desc,location:loc}).getid(); row[8] = newevent; // update data array event id } else { event.settitle(title); event.setdescription("("+status+") "+desc); event.setlocation(loc); // event.settime(tstart, tstop); // cannot settime on eventseries. // ... can set recurrence! var recurrence = calendarapp.newrecurrence().adddailyrule().times(1); event.setrecurrence(recurrence, tstart, tstop); } data[i]=row;// update data row values otherwise lost ! } logger.log(data); var data1 = []; var data2 = []; for(var n in data){ data1.push(data[n].slice(0,8)); data2.push(data[n].slice(8,9)); } logger.log('data1 = '+json.stringify(data1)); logger.log('data2 = '+json.stringify(data2)); sheet.getrange(1,1,data1.length,data1[0].length).setvalues(data1); // write below check writes !!! sheet.getrange(1,9,data2.length,data2[0].length).setvalues(data2); // change row6 1 whan copying in real code !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! // record event ids spreadsheet except row 7 } 
Comments
Post a Comment