CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Alok Kumar
Alok Kumar

Reputation: 11

Copy Data into Spreadsheet Last Active Column but in Specific Rows

I'm trying to import the data on a daily basis in the columns. However, top Rows of spreadsheet are filled with formulas till month end dates and I need to copy the data into columns after x number of rows. When using getLastColumn, data is being pasted into last column which is last date of sheet. I'm unable to get the correct result. I'm using the below code for now -

function Import() {
  var source = SpreadsheetApp.openById('ABC');
var sheet = source.getSheetByName('Export');
var copy1 = sheet.getRange(1,2,5000,1).getValues();

var destination = SpreadsheetApp.openById('XYZ');
var final_destination = destination.getSheetByName('Import')


var paste1 = final_destination.getRange(7,final_destination.getLastColumn(),5000,1).setValues(copy1);

}

Below is the link of sample spreadsheet for reference - https://docs.google.com/spreadsheets/d/1apoZkPMpggqsMJd78uZIP5YlJHyCn0PTfwvZQUgsobk/edit?usp=sharing

I want to copy data from row 7 on daily basis. On next day, data will be pasted in column L, next day in column M and so on.

Kindly help with the same. Thanks.

Upvotes: 0

Views: 61

Answers (2)

Alok Kumar
Alok Kumar

Reputation: 11

I got the solution by below code. Thanks to all for suggestions.

function Import() {
  var source = SpreadsheetApp.openById('ABC');
var sheet = source.getSheetByName('Export');
var copy1 = sheet.getRange(2,2,5000,1).getValues();

var destination = SpreadsheetApp.openById('XYZ');
var final_destination = destination.getSheetByName('Total Land Daily Actual')
var lastRow = final_destination.getRange(14,1).getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();

var paste1 = final_destination.getRange(14,lastRow+1,5000,1).setValues(copy1);

}

Upvotes: 1

Cooper
Cooper

Reputation: 64032

Try this:

function Import() {
  var source = SpreadsheetApp.openById('ABC');
  var sheet = source.getSheetByName('Export');
  var copy1 = sheet.getRange(1, 2, sheet.getLastRow() , 1).getValues();
  var destination = SpreadsheetApp.openById('XYZ');
  var final_destination = destination.getSheetByName('Import')
  var paste1 = final_destination.getRange(7, final_destination.getLastColumn(), copy1.length, 1).setValues(copy1);
}

Upvotes: 0

Related Questions