如何设置除一个之外的所有列的值？ [英] How to set values for all columns except one?

问题描述

我的代码效果很好，它所做的只是从电子表格中获取事件并在Google日历上创建它们。
但是，我的电子表格中的一列包含一个公式。每次运行代码时，公式都会消失，并被该单元格上的任何内容替换。

我知道这是问题所在：

  //将所有事件ID记录到电子表格中，第7行除外
 range.setValues（data）; 
  

你如何编写一个循环来将其应用于行[0]到行[8]跳过行[7]？

但是这里是完整的代码供参考：

  function onOpen（）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export Events，
 functionName：exportEvents
}]; 
 sheet.addMenu（更新日历，条目）; 
} 
 
 //将事件从电子表格导出到日历// 
函数exportEvents（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 1; //标题信息的行数（跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =calendar_ID; 
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 if（i  var row = data [i]; 
 var date = new Date（row [0]）; //第一列
 var title = row [1]; //第二列
 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]; //八列== eventId 
 //检查事件是否已经存在，如果有，则更新它
 try {
 var event = cal.getEventSeriesById（id）; 
} 
 catch（e）{
 //什么都不做 - 我们只是想避免在事件不存在的情况下发生异常
} 
 if（！event） {
 //cal.createEvent(title，new Date（March 3，2010 08:00:00），new Date（March 3，2010 09:00:00），{description：desc，位置：LOC}）; 
 var newEvent = cal.createEvent（title，tstart，tstop，{description：（+ status +）+ desc，location：loc}）。getId（）; 
 row [8] = newEvent; //用事件ID 
更新数据数组
 else {
 event.setTitle（title）; 
 event.setDescription（（+ status +）+ desc）; 
 event.setLocation（loc）; 
 // event.setTime（tstart，tstop）; //无法在eventSeries上设置时间。 
 // ...但我们可以设置重复！ 
 var recurrence = CalendarApp.newRecurrence（）。addDailyRule（）。times（1）; 
 event.setRecurrence（recurrence，tstart，tstop）; 
} 
调试器; 
} 
 //将所有事件ID记录到电子表格（第7行除外）
 range.setValues（data）; 
 
  

解决方案

没有办法写选择性地使用setValues ...我建议你用2个新数组拼接你的数组（垂直方向），并用2个不同的setValues来写它，这样公式的列就不会改变。

请注意，拼接阵列方法会水平拼接（对于二维数组），所以您必须循环进入第一级，然后为每一行进行拼接，但使用阵列的速度非常快，所以它不会成为问题。

示例代码:(我从一张表中获取数据来演示）

 函数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））; //剪下第8列的行并保留1 
 data1。 push（data [n] .splice（0,6））; //在0处截断并保持6，此方法将数组切割>> get data2 before data1 
} 
 Logger.log（'data1 ='+ JSON.stringify（data1））; 
 Logger.log（'data2 ='+ JSON.stringify（data2））; 
} 
  

---

编辑：

将其插入代码中，就像这样使用它：

  sh.getRange（1,1 （data1.length，data2 [0] .length）.setValues，data1.length， （data2）; // update col 8 
  

顺便说一下，你也可以使用slice方法...下面是一个使用工作表更新的完整测试：

 函数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编辑：完整代码在你的实现中 h2>

因为看起来你没有得到它的工作，下面是一个完整的实现。在此表单上

  function onOpen（）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export Events，
 functionName：exportEvents
}]; 
 sheet.addMenu（更新日历，条目）; 
} 
 
 //将事件从电子表格导出到日历// 
函数exportEvents（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 1; //标题信息的行数（跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; //原有目的删除链接
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 if（i  var row = data [i]; 
 var date = new Date（row [0]）; //第一列
 var title = row [1]; //第二列
 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]; //八列== eventId 
 //检查事件是否已经存在，如果有，则更新它
 try {
 var event = cal.getEventSeriesById（id）; 
} 
 catch（e）{
 //什么都不做 - 我们只是想避免在事件不存在的情况下发生异常
} 
 if（！event） {
 //cal.createEvent(title，new Date（March 3，2010 08:00:00），new Date（March 3，2010 09:00:00），{description：desc，位置：LOC}）; 
 var newEvent = cal.createEvent（title，tstart，tstop，{description：（+ status +）+ desc，location：loc}）。getId（）; 
 row [8] = newEvent; //用事件ID 
更新数据数组
 else {
 event.setTitle（title）; 
 event.setDescription（（+ status +）+ desc）; 
 event.setLocation（loc）; 
 // event.setTime（tstart，tstop）; //无法在eventSeries上设置时间。 
 // ...但我们可以设置重复！ 
 var recurrence = CalendarApp.newRecurrence（）。addDailyRule（）。times（1）; 
 event.setRecurrence（recurrence，tstart，tstop）; 
} 
 data [i] = row; //用行值更新数据，否则会丢失！ 
} 
 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）; //在下面写下来检查它在哪里写入！ 
 sheet.getRange（1,9，data2.length，data2 [0] .length）.setValues（data2）; //将row6更改为1 whan复制实际代码!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 
 //将所有事件ID记录到电子表格，第7行除外
} 
  

My code works great, all it does is take events from a spreadsheet and creates them on google calendar. However, one of the columns in my spreadsheet contains a formula. Everytime I run the code, the formula disappears and is replaced by whatever is on that cell at the time.

I know this is where the issue is:

   // Record all event IDs to spreadsheet except for row 7
   range.setValues(data);

How can you write a loop to only apply this from row[0] to row[8] but skipping row [7]?

But here is the full code for reference:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Update Calendar", entries);
}

//Export events from spreadsheet to 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);
  for (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];              // Eight column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the 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 the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription("("+status+") "+desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet except for row 7
  range.setValues(data);
}

解决方案

There is no way to write "selectively" using setValues... I suggest you splice your array (vertically) in 2 new arrays and write it with 2 different setValues, leaving the formula's column untouched.

Note that the splice array method splices horizontally (for a 2D array) so you'll have to loop into the first level and do the splicing for each row but working with arrays is very fast so it won't be an issue.

example code :(I took data from a sheet to 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 the row at col 8 and keep 1
    data1.push(data[n].splice(0,6));// cut at 0 and keep 6 , this method cut the array >> get data2 before data1
  }
  Logger.log('data1 = '+JSON.stringify(data1));
  Logger.log('data2 = '+JSON.stringify(data2));
}

---

EDIT :

to insert it in your code simply use it like this :

  sh.getRange(1,1,data1.length,data1[0].length).setValues(data1);// update col 1 to 6
  sh.getRange(1,8,data2.length,data2[0].length).setValues(data2);// update col 8

Btw, you can also use slice method ... below is a complete test with 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 it seems you didn't get it working, below is a full implementation. tested on this sheet

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Update Calendar", entries);
}

//Export events from spreadsheet to 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 = "[e-mail]"; //removed link on purpose
  var cal = CalendarApp.getCalendarById(calId);
  for (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];              // Eight column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the 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 the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription("("+status+") "+desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    data[i]=row;// update data with row values otherwise it is 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 to check where it writes !!!
  sheet.getRange(1,9,data2.length,data2[0].length).setValues(data2); // change row6 to 1 whan copying in real code !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  // Record all event IDs to spreadsheet except for row 7
}

这篇关于如何设置除一个之外的所有列的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！