0

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.

so row

1a 1b 1c

2a 2b 2c

3a 3b 3c

will become...

1a

1b

1c

2a

2b

2c

etc...

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++){

  }
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
Justin Lowfer
  • 3
  • 2

3 Answers3

1

You don't need script. Just put this formula in a cell:

=transpose(split(join(" ",A1:A3)," "))

You can join multiple rows and columns. I tested this on data like you provided in column A & B rows 1 to 3. Works fine.

=transpose(split(join(" ",A1:A3,B1:B3)," "))
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • Thanks for the tip. I need to be able to grab several rows and columns at a time. I can only seem to get this to work with one row at a time. – Justin Lowfer Feb 24 '16 at 23:47
0

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.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • Hi Sandy thanks for the response, I'm getting an error Cannot find method getRange(number,number,number). (line 7, file "Code") – Justin Lowfer Feb 24 '16 at 23:45
  • See updated answer. Change lines to this: `var ss =spreadsheetApp.getActive();var sheet = ss.getActiveSheet();` – Alan Wells Feb 24 '16 at 23:49
  • Fixed arror by changing sheet.getRange to sheet.getActiveSheet().getRange. Have a new error now Cannot convert Array to Object in the last line. – Justin Lowfer Feb 24 '16 at 23:59
  • `Sheet` had an upper case `S`. Change to `sheet` and see if that works. – Alan Wells Feb 25 '16 at 00:02
  • Fixed that, giving me the error cannot convert array to object. – Justin Lowfer Feb 25 '16 at 00:08
  • See updated answer. Add `Logger.log()` statements, run the code, and VIEW the LOGS. Look for unexpected results. – Alan Wells Feb 25 '16 at 00:27
  • Array looks good, just a list of names which is good. I think it has something to do with .setValues(outArray). Error is: Cannot convert Array to Object[][]. (line 20, file "Code"). Is it because outerArray is one dimensional and setValues is expecting a 2D array? – Justin Lowfer Feb 25 '16 at 00:37
  • Change the last, 4th parameter from `1` to `outerArray[0].length`: Change to: `sheet.getRange(1,1,outerArray.length,outerArray[0].length).setValues(outerArray);` Are there any rows with empty cells? – Alan Wells Feb 25 '16 at 00:42
  • Changed .setValues to .setValue run now. Still need to test to see if it works how I need it too. Thanks for your help. – Justin Lowfer Feb 25 '16 at 00:44
  • If this answers you question, you can mark it as correct by clicking the big green check mark. – Alan Wells Feb 25 '16 at 01:03
  • It's close but doesn't work quite right. Changing .setValues to .setValue only copies the first element in the array. Leaving .setValues continues to give me an error. – Justin Lowfer Feb 25 '16 at 01:06
  • If you check the final array: `Logger.log('outerArray: ' + outerArray);` Does it look like it should? Everything in there? Add that after the `for` loop. – Alan Wells Feb 25 '16 at 03:00
0

This seems to work. Manually iterates through the array and sets it with a for loop.

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

  var numRows = sheet.getLastRow();
  var rowIdx = sheet.getActiveRange().getRowIndex();
  var colIdx = sheet.getActiveRange().getColumnIndex();

  var theValues = sheet.getRange(rowIdx,colIdx,sheet.getLastRow(),3).getValues();

  theValues = theValues.toString().split(",");//convert 2D array to 1D

  var outerArray = [];

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

//  sheet.getRange(1,1,outerArray.length,30).setValues(outerArray);

  for(var i = 0; i < outerArray.length; i++){
    sheet.getRange(30 + i, 1).setValue(outerArray[i]);
  }
};
Justin Lowfer
  • 3
  • 2