将行和列数据转换为仅列

问题描述

我是Google脚本的新手，请多多包涵.我正在尝试编写一个脚本，该脚本将从几行和几列中获取数据，然后将其重写为具有间隔的单列.

I'm completely new to Google Script so bear with me. I'm attempting to write a script that will take data from several rows and columns and then rewrite it into a single column with spacing.

如此行

1a 1b 1c

2a 2b 2c

3a 3b 3c

将成为...

1a

1b

1c

2a

2b

2c

等...

到目前为止，我还没有任何东西.我不明白如何在数组中访问每个不同的数据.任何帮助/指导将不胜感激.

I don't really have anything so far. I don't understand how each different piece of data is being accessed in the array. Any help/guidance would be appreciated.

function copyRow(){
  var sheet = SpreadsheetApp.getActive();
  var numRows = sheet.getDataRange().getNumRows();
  var rowIdx = sheet.getActiveRange().getRowIndex();
  sheet.getRange(rowIdx, 1, 1, sheet.getLastRow()).getValues();
  for(var i = 0; i < numRows; i++){

  }
}

推荐答案

尝试此代码. getValues()方法获取一个二维数组.每个内部数组都是一个新行.每个内部数组的每个元素都是新列中的单元格. 2D数组可以折叠为常规数组，没有外部数组.但是要写入值，需要一个2D数组，因此下面的代码创建了一个新的2D数组.每个内部数组只有一个值.

Try this code. The getValues() method gets a 2 dimensional array. Every inner array is a new row. Every element of each inner array is a cell in a new column. The 2D array can be collapsed into a regular array, with no outer array. But to write the values, a 2D array is needed, so the code below creates a new 2D array. Every inner array only has one value.

function copyRow() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();

  var numRows = sheet.getLastRow();
  var rowIdx = sheet.getActiveRange().getRowIndex();
  Logger.log('rowIdx: ' + rowIdx);

  //getRange(start row, start column, number of rows, number of columns)
  var theValues = sheet.getRange(rowIdx,1,sheet.getLastRow()).getValues();
  Logger.log('theValues.length: ' + theValues.length);

  theValues = theValues.toString().split(",");//convert 2D array to 1D
  Logger.log('theValues.length: ' + theValues.length);

  var outerArray = [];

  for(var i=0; i<theValues.length; i++){
    Logger.log('theValues[i]: ' + theValues[i]);
    outerArray.push(theValues[i]);
  };

  Logger.log('outerArray.length: ' + outerArray.length);
  sheet.getRange(1,1,outerArray.length,outerArray[0].length).setValues(outerArray);
};

尝试一下，看看它是否有效，然后告诉我.

Try it and see if it works, and let me know.

这篇关于将行和列数据转换为仅列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！