CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mel
Mel

Reputation: 15

Google Apps Script Find bottom of column

I need some assistance with Google Apps Script. I am working in google sheets and currently have the following script:

function transpose() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C3:N3').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet21'), true);   
   spreadsheet.getRange("'A/P'!C3:N3").copyTo(spreadsheet.getActiveRange(), 
  SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};

Rather than just paste in A1 of Sheet 21, I would like it to find the bottom of column F. I am also wondering how to copy data from whichever sheet I am in, not just the sheet named A/P.

Thank you in advance!

Upvotes: 1

Views: 903

Answers (1)

Tanaike
Tanaike

Reputation: 201503

You want to add the values of C3:N3 in the active sheet to the next row of last row on column F in Sheet21. If my understanding is correct, how about this modification?

Modification points :

  • In your script, if the active sheet is not Sheet21, the active range becomes "A1", because spreadsheet.getRange('C3:N3').activate() at other sheet is changed by spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet21'), true). By this, the values of "'A/P'!C3:N3" are copied for "A1" of Sheet21.

Flow of modified script:

  • Retrieve the range of source (Active sheet)
  • Retrieve the destination (Sheet21) sheet.
  • Retrieve the destination range.
  • Copy

Modified script : Pattern 1

If the address of last row of column "F" in "Sheet21" is smaller than that of other columns, please use this.

function transpose() {
  var spreadsheet = SpreadsheetApp.getActive();
  var srcRange = spreadsheet.getActiveSheet().getRange('C3:N3');
  var dstSheet = spreadsheet.getSheetByName('Sheet21');
  var range = dstSheet.getRange('F1:F');
  var values = range.getValues();
  var formulas = range.getFormulas();
  var i;
  for (i = values.length - 1; i >= 0; i--) {
    if (values[i][0] != "" || formulas[i][0] != "") break;
  }
  var dstRange = dstSheet.getRange("F" + (i + 2));
  srcRange.copyTo(dstRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};

Modified script : Pattern 2

If the address of last row of column "F" in "Sheet21" is larger or the same with that of other columns, please use this.

function transpose() {
  var spreadsheet = SpreadsheetApp.getActive();
  var srcRange = spreadsheet.getActiveSheet().getRange('C3:N3');
  var dstSheet = spreadsheet.getSheetByName('Sheet21');
  var dstRange = dstSheet.getRange("F" + (dstSheet.getLastRow() + 1));
  srcRange.copyTo(dstRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};

If I misunderstand your question, please tell me. I would like to modify it.

Upvotes: 1

Related Questions