copy and paste to next available Column in a certain Row Google Sheets Macro-CodePudding

I have a range of data from - I3:I17

In the macro, I need to copy this range and paste it in the next available column. The dedicated space for pasting begins at N3, then continues on for 52 columns, after that it needs to start back at N3. It is for a weekly average of spending over a year.

In the macro I have recorded, it copies and pastes the range into the desired (first) position N3, however once I click the command button again it re-pastes into the exact same place i.e. overwriting the original paste. I need the next iteration to paste in the next available cells to the right.

Here is my code from the recording of the Marco;

function Averagespendingcopy() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('N3').activate();
  spreadsheet.getRange('I3:I17').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

I have also attached a spreadsheet link if that helps:

https://docs.google.com/spreadsheets/d/1o_-vRKTzwQKMpypCp7OxgNbAnVVAYMueFCoozfqK41M/edit?usp=sharing

CodePudding user response：

I believe your goal is as follows.

On the active sheet, you want to copy the values from "I3:I17" after Column "N".
For example, after the values of "I3:I17" was copied to the cell "N3", when the script is run again, you want to copy the values of "I3:I17" to the cell "O3".
When the columns "N" to "BM" are not empty, you want to copy the values of "I3:I17" to the cell "N3" again.

In your script, unfortunately, the values of "I3:I17" are always copied to the cell "N3".

In order to achieve your goal, how about the following sample script?

Sample script:

function myFunction() {
  var start = 14; // Column "N"
  var end = 66; // Column "BM"
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(3, start, 15, end - start);
  var values = range.getValues();
  var idx = values[0].map((_, c) => values.map(r => r[c])).findIndex(r => r.join("") == "");
  var col = idx == -1 ? start : idx   start;
  // if (idx == -1) range.clearContent();
  sheet.getRange('I3:I17').copyTo(sheet.getRange(3, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

When this script is run, when the script is run, the 1st empty row after the column "N" is searched and the values of "I3:I17" are put into the searched column. And, when the columns "N" to "BM" are not empty, when the script is run, the values of "I3:I17" are copied to the cell "N3".
- In this sample, 14 rows of each column are searched. When those 14 rows are empty, the column is used as the destination column.
When the columns "N" to "BM" are not empty, when the script is run, the values of "I3:I17" are copied to the cell "N3". At that time, when you want to clear the columns "N" to "BM", please use if (idx == -1) range.clearContent();. In this current script, the cells are not cleared.

Reference:

findIndex()