移位Google Spreadsheet中的值 [英] Shift values in Google Spreadsheet

问题描述

我正在制作一个电子表格，该电子表格将用于预订我所属俱乐部的设备.我正在寻找一种方法，用于将电子表格的选定区域中的所有单元格在午夜时一个移动到左侧，并将所有单元格一直删除到左侧.我知道我可以使用触发器来执行此操作，但是我不确定如何编写函数来执行此操作.

I am making a spreadsheet that is to be used for booking equipment for a club I am part of. I am looking for a way for all the cells in a selected area of a spreadsheet to be moved one to the left at midnight, and ones all the way to the left deleted. I understand that I can just use a trigger to execute this, however I am not sure how to go about writing the function to do this.

任何帮助或建议将不胜感激.

Any help or suggestions would be appreciated.

推荐答案

如果您具有一个可以将二维数组的内容左移的函数，这将很简单.这样，您可以执行以下操作.有关方法的详细信息，请参见文档.

This would be simple if you had a function that could left-shift the contents of a two-dimensional array. With that, you could do something like the following. Refer to the documentation for method details.

• 使用Sheet.getRange()定义要移动的范围.

• Define the range you want shifted, using Sheet.getRange().

使用Range.getValues()获取包含所有要移位数据的数组.

Use Range.getValues() to get an array with all the data you want to shift.

移动数组内容.

var result = [];
for (var r=0; r<array2d.length; r++) {
  var row = array2d[r].slice(0);
  var rotVal = row.shift();
  row.push(''); // preserve row width by adding a blank value
  result.push(row);
}

使用Range.setValues()将移位后的数组值写到先前的范围.

Write the shifted array values out to the previous range, using Range.setValues().

注意:只有值将以这种方式保留，没有公式，没有格式.如果要格式化，则可以获取它的数组并以类似的方式对其进行操作.由于相对引用的影响，公式比较棘手.

Note: Only values will be preserved this way, no formulas, no formatting. If you want formatting, you could get arrays of it and manipulate it in a similar fashion. Formulas are trickier, due to the effect of relative references.

上面的代码段摘录了此函数，该函数可以向左，向右，向上或向下移动数组，并且还支持旋转"概念(将从行或列的一端拉出的值放在另一端，而不是空白).为了确保源数组保持不变，它依赖于 deepCopy()函数.

That snippet above is an excerpt of this function, which can shift an array left, right, up or down, and also supports the "rotate" concept (where a value pulled from one end of a row or column is placed at the other end, instead of a blank). To ensure that the source array is left untouched, it relies on a deepCopy() function.

/**
 * Shift the contents of a two-dimensional array in given direction,
 * with rotate option. Can be used as a spreadsheet custom function.
 * Dimensions of resulting array correspond to input array.
 *
 * Written for http://stackoverflow.com/questions/17404787 by Mogsdad
 *
 * @param {Array}   array2d    Two-dimensional array input
 * @param {String}  direction  One of {'left', 'right', 'up', 'down'},
 *                             defaults to 'left'.
 * @param {Boolean} rotate     Set true if shifted value should be
 *                              rotated to other end, default false.
 *
 * @returns {Array}            Two-dimensional array result
 */
function shiftArray( array2d, direction, rotate ) {
  direction = direction || 'left';
  rotate = rotate || false;
  var result = [];

  switch (direction) {
    case 'left':
      for (var r=0; r<array2d.length; r++) {
        var row = array2d[r].slice(0);
        var rotVal = row.shift();
        row.push(rotate ? rotVal : '');
        result.push(row);
      }
      break;

    case 'right':
      for (var r=0; r<array2d.length; r++) {
        var row = array2d[r].slice(0);
        var rotVal = row.pop();
        row.unshift(rotate ? rotVal : '');
        result.push(row);
      }
      break;

    case 'up':
      result = deepCopy(array2d);
      var rotRow = result.shift();
      if (!rotate)
        // empty all elements in rotRow
        for (var c=0; c<rotRow.length; c++)
          rotRow[c]='';
      result.push(rotRow);
      break;

    case 'down':
      result = deepCopy(array2d);
      var rotRow = result.pop();
      if (!rotate)
        // empty all elements in rotRow
        for (var c=0; c<rotRow.length; c++)
          rotRow[c]='';
      result.unshift(rotRow);
      break;

    default:
      throw new Error( "Unknown direction '"+direction+"'" );
  }
  return result;
}

/**
 * Return a deep copy of the given object.
 *
 * From: http://james.padolsey.com/javascript/deep-copying-of-objects-and-arrays/#comment-10679
 */
function deepCopy(o) {
    var copy = o,k;

    if (o && typeof o === 'object') {
        copy = Object.prototype.toString.call(o) === '[object Array]' ? [] : {};
        for (k in o) {
            copy[k] = deepCopy(o[k]);
        }
    }
    return copy;
}

这篇关于移位Google Spreadsheet中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！