1

I'm trying to write a script to paste rows of cells from one sheet to another while ignoring rows with blank cells in Column A. The script would also swap the order of Columns B and C. Here is what I am trying to do:

INPUT     Sheet....................................OUTPUT  Sheet 
A1= ID    B1= Last Name       C1 = MI              A1= ID  B1 = MI C1= Last Name   
A2= 1     B2= Stewart         C2= M................A2= 1   B2= M   C2= Stewart
A3=       B3= Smith           C3= R................A3= 4   B3= V   C3= Holland   
A4= 4     B4= Holland         C4= V................A4= 3   B4= B   C4= Young
A5= 3     B5= Young           C5= B

Here is what I have:

function removeEmptyRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input");
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Output");
var data = sheet.getRange("A2:D").getValues();
var newData = new Array();
for(i in data){
var row = data[i];
var empty = false;
for(i in data){
  if(row[0] == ""){
    empty = true;
  }
}
if(!empty){
  newData.push(row);
}
sheets.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
};}

I have not been able to figure out how to incorporate changing the order of columns.

I was not able to apply previous posts to this problem, although this one came closest.

I would really appreciate any help you can provide. Thank you!

Dustin
  • 59
  • 7
  • (include your code in your question, and do *not* link to live editable documents - they aren't useful to anyone with a similar question!) – tehhowch Jun 25 '18 at 23:50
  • Thank you, @tehowch. I updated by comments and removed the live sheet, but I don't have any code. – Dustin Jun 26 '18 at 00:11
  • You'll need to make an attempt first - this is not a code shop. Consider reviewing available methods for Google Sheets in the Apps Script "Spreadsheet Service" documentation, and also for `Array`s in your preferred JavaScript language reference (such as Mozilla Developer Network) – tehhowch Jun 26 '18 at 00:41
  • Look near this line of code `newData.push(row);`. Here is where you are adding rows based on the condition that if Column A is not empty. You need to reverse your column order in your `row` here before pushing it to `newData`. – Suhail Ansari Jun 26 '18 at 07:11
  • As @suhail mentions, you are blindly inserting the row data into the output. If you have just 3 columns to transfer, you can explicitly build a new array to use: `newData.push( [ row[0], row[2], row[1] ] );` If you have more than a handful of columns and the rest do not need to be modified, you would use some `Array` methods like `slice`, `concat`, and/or `splice` to simplify the construction. – tehhowch Jun 26 '18 at 13:47
  • @ Suhail Ansari, @tehhowch. Thanks both for your help. The hint and especially sample code really help. Do either of you have any recommendations for learning only the java/app script that is applicable to Google Sheets? I don't want to learn all of Javascript if I don't have to. Thank you. – Dustin Jun 26 '18 at 21:55
  • Note that Google Apps Script does not implement the latest JavaScript language features (such as arrow functions, `class ___`, `Promise`s, `Map()`, `Set()`, or `async`/`await`) - it is JS v1.6 with some window dressing. It is compatible with most programming practices. Note that if you are building `HtmlService` outputs, the JS you can write for the client-side HTML will run whatever bleeding-edge JS functionality the user's browser supports. Learn `Array`s, the fundamental `Object`, and you'll be well equipped to work with the Google-specific classes exposed in GAS. – tehhowch Jun 26 '18 at 22:51
  • Great. Thank you. – Dustin Jun 27 '18 at 12:24
  • @tehhowch. When A2 is blank, I receive the following error: "TypeError: Cannot read property "length" from undefined." The problem is in my last line of code with letters (i.e. sheets.getRange(2,1........). I'd greatly appreciate any tips here. If A2 is not blank, then the code does exactly what I want, and I am able to modify it to test different columns (e.g. B, C, D). Thank you. – Dustin Jun 28 '18 at 13:36
  • @Dustin you should reevaluate your `for` loop contents. for example, you have two - why? One is sufficient. Regarding the `TypeError`, if `newData` has length 0 (i.e. no contents), then `newData[0]` is `undefined`, and `undefined.length` correctly raises an error. – tehhowch Jun 28 '18 at 13:47
  • Thank you, @tehhowch. If I remove the second for loop, I get the following error"TypeError: Cannot read property "length" from undefined. (line 256, file "Code")." As far as the error that pops up when A2 is blank, how would I modify that last line of code? When I change to length 1, I still get an error. – Dustin Jun 28 '18 at 14:05
  • For example, how about modifying ``newData`` to ``var newData = data.filter(function(e){return e[0]}).map(function(e){return [e[0], e[2], e[1], e[3]]})``? This condition is that the cells which have the values at column A of "INPUT" retrieved, and the data is reordered. Please customize this for your environment. I'm not sure about ``D`` of ``"A2:D"``, because the column D is not used in your sample input and output. So added the column D to the last column of ``newData``. If this was not what you want, I'm sorry. – Tanaike Jun 30 '18 at 09:13

0 Answers0