CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sony Mitto
Sony Mitto

Reputation: 145

Google Apps Script - How to copy a column(s) to another sheet at next available column

I have a requirement where I need to take values from one column (or more) and copy them to another sheet in the next available column (s). I have written a script like this. It does copy the values from one column but it has no way to move forward for taking another snapshot of new data in same source column to destination sheet's next free column. 

*//keep a copy of Sales numbers for every month
function readSalesNum() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales plan");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SalesRecordsMonthly");

// Copy from 17th row, 4th column, all rows for one column 
var rangeToCopy = sheetFrom.getRange(17, 4, sheetFrom.getMaxRows(), 1);

//Paste to another sheet from first cell onwards
rangeToCopy.copyTo(sheetTo.getRange(1, 1));
}*

I am sorry about the poor formatting :( I need to modify this script to mention any set of columns from source sheet and copy them to destination sheet. And for new month, it should do the same in next set of columns, instead of overwriting as it does now. Also, the copy should happen only for values which is missing yet. I know there's an option of ContentOnly in script but not sure how to use it.

Upvotes: 4

Views: 26131

Answers (3)

Anubhav Yadav
Anubhav Yadav

Reputation: 173

The entire 4th column & 17th row onward can be appended as a column into a different sheet with this approach also.

  var sheetfrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetfrom');
  var sheetto = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetto');

  sheetfrom.getRange(17, 4, sheetfrom.getLastRow(), 1).copyTo(sheetto.getRange(1, 
  sheetTo.getLastColumn()+1, sheetfrom.getLastRow()-17, 1), {
    contentsOnly: true
  });

This also overcomes the empty cell problem of copying data from some Formula outputs.

Upvotes: 0

Serge insas
Serge insas

Reputation: 46794

If I understood correctly, here is a code that does what you wanted, ie get the values from one sheet in column4 from row 17 and copy it to the other sheet without overwriting to columns starting at row 1

function readSalesNum() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales plan");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SalesRecordsMonthly");

// Copy from 17th row, 4th column, all rows for one column 
var valuesToCopy = sheetFrom.getRange(17, 4, sheetFrom.getLastRow(), 1).getValues();

//Paste to another sheet from first cell onwards
sheetTo.getRange(1,sheetTo.getLastColumn()+1,valuesToCopy.length,1).setValues(valuesToCopy);
}

test sheet here, make a copy to run the script - view only

This covers only the first part of your question, the second part was a bit confusing (as mentioned in my comment above).

Upvotes: 8

AshClarke
AshClarke

Reputation: 3078

Try this

*//keep a copy of Sales numbers for every month
function readSalesNum() {
  var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales plan");
  var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SalesRecordsMonthly");

  // Copy from 17th row, 4th column, all rows for one column 
  var rangeValues = sheetFrom.getRange(17, 4, 1, sheetFrom.getMaxRows()).getValues();

  //Paste to another sheet from first cell onwards

  sheetTo.appendRow(rangeValues[0]);

}

This will do what you want with the range you suggested. However, looks like you're trying to get two different ranges so you'll have to accommodate that.

Upvotes: -2

Related Questions