google spreadsheet - How can I use IMPORTRANGE to populate my create calendar event sheet without affecting the script? -

-

this formula in a2 , populates data in booking sheet

=query(    importrange("sheet_key","sheet1!a7:z"),    "select col7,col2,col8,col9,col12,col19"  )

the calendar event script https://stackoverflow.com/a/15790894/6400958 works , takes 5-10 seconds run when data entered directly creates duplicates , exceeds run time when import range formula used same amount of data.

here demo file. note there 3 rows of event data.

below script:

 /**   * adds custom menu active spreadsheet, containing single menu item   * invoking exportevents() function.   * onopen() function, when defined, automatically invoked whenever   * spreadsheet opened.   * more information on using spreadsheet api, see   * https://developers.google.com/apps-script/service_spreadsheet   */ function onopen() {   var sheet = spreadsheetapp.getactivespreadsheet();   var entries = [{     name : "export events",     functionname : "exportevents"   }];   sheet.addmenu("calendar actions", 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 = "my_calendar_id";   var cal = calendarapp.getcalendarbyid(calid);   (i=0; i<data.length; i++) {     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 id = row[6];              // sixth 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:desc,location:loc}).getid();       row[6] = newevent;  // update data array event id     }     else {       event.settitle(title);       event.setdescription(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   range.setvalues(data); }

it's open ended reference ("sheet1!a7:z") makes resulting range of var range = sheet.getdatarange(); includes lot of blank rows. add clause filter out blank rows, where col2 <> ''. final formula following

 =query(   importrange("sheet_key","sheet1!a7:z"),   "select col7,col2,col8,col9,col12,col19 col2 <> ''"  )

Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more