0

I'm trying to do the following in a google sheet:

Problem 1:

Cell B2, B3 & B4 have the data I want to copy into the last row of Columns C, D, & E

The following code below works fine, it just gets the last row of the sheet, and I can't localize it to those columns.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sinput = ss.getSheetByName("DEPOSITS_WITHDRAWLS");
  var soutput = ss.getSheetByName("DEPOSITS_WITHDRAWLS");
  var input = sinput.getRange(2, 2, 3).getValues();
  sinput.getRange(3,2, 2).clear();
  var flatin = [].concat.apply([], input);
  soutput.getRange(soutput.getLastRow()+1, 3,1,3).setValues([flatin]);
  soutput.insertRowAfter(soutput.getLastRow());
  Logger.log(input);

Problem 2:

I made a drop-down in cell B1 which has "Option 1" & "Option 2"

I want to make an if/else out of it. The only difference is the data will either go to the last rows of columns C, D, & E for "Option 1" or go to the last rows of columns G, H, & I for "Option 2". The following code looooks like it would work, but I don't really recognize the language. Is it for excel? Either way, I feel like both of these are close, but I'm completely lost on how to implement them. Any help is greatly appreciated. Thanks and have a great day.

If optMemberName.Value = True Then
    With Sheets("Sheet1")
        Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row) = txtMemberName.Text
    End With
ElseIf optMemberID.Value = True Then
    With Sheets("Sheet1")
        Range("B" & .Cells(.Rows.Count, "B").End(xlUp).Row) = txtMemberID.Text
    End With
End If
Marios
  • 26,333
  • 8
  • 32
  • 52
John Jackowiak
  • 31
  • 5

1 Answers1

0

Explanation / Issue:

  • For the first problem, I believe the issue has to do with the soutput.getLastRow() line. The latter gets the last row with content in the entire sheet. Meaning that if the last row with content in your sheet is in column A, for example in cell A500, then the aforementioned expression will give you 500 regardless if the last row of C, D or E is different. Assuming that C, D and E have the same last row with content, you can use this approach to find the last row of a particular column.

  • For the second problem, the code you posted has nothing to do with javascript. You can see the two code snippets you posted are way different in terms of structure and formatting. But the logic is kinda there. You need an if condition to check whether cell B1 contains "Option 1" or "Option 2" and according to this value paste to the corresponding range.

  • I also optimized your code a little bit. Since sinput and soutput concern the same exact sheet, there is no reason of using two different variables. You can just use one and name it sheet. Also I see some lines of code which you haven't described in the description of your goal. I removed the row you clear some values and the row you add an additional row. Feel free to add those, but respect the logic of the script.

Solution:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DEPOSITS_WITHDRAWLS");
  var input = sheet.getRange(2, 2, 3).getValues().flat(); 
  
  var option = sheet.getRange('B1').getValue();
  
  if (option == 'Option 1'){
  var Lrow = sheet.getRange('C:C').getValues().filter(String).length+1;
  sheet.getRange(Lrow, 3,1,3).setValues([input]);
  sheet.insertRowAfter(sheet.getLastRow());
  }
  
  else {
  var Lrow = sheet.getRange('G:G').getValues().filter(String).length+1;
  sheet.getRange(Lrow, 7,1,3).setValues([input]);
  sheet.insertRowAfter(sheet.getLastRow());
  }
  
}
Marios
  • 26,333
  • 8
  • 32
  • 52