-1

I wonder if you could please help me solve this. My knowledge of scripts is quite poor, despite attempts to learn.

This is an example of my spreadsheet.

Each month, in the Monthly sheet, I copy data from all the rows below the frozen ones (columns B, C and D only) and make a note in my head of the number of rows copied (the figure shown in cell E2, which can vary). I then go to the Daily sheet, insert the required number of rows at the top of the unfrozen section, and paste in the data values (CTRL Shift V).

I've been trying unsuccessfully to automate this with a script. I've tried piecing bits of code together from other solutions, but the different methods used and my lack of knowledge mean I can't get them to work.

Hoping you can help.

Many thanks.

AP68
  • 3
  • 3
  • Hi, welcome to stack overflow. Normally when you ask questions, please include a code snippet and a more specific question, I'd recommend taking [the tour](https://stackoverflow.com/tour). Despite this, I plan on answering your question despite this, as long as its not closed, because it should not be too difficult to do. – Baby_Boy Jan 21 '21 at 14:06
  • Thanks Baby-Boy. Understood. I'll see what i can do regarding a code snippet and post it. Many thanks. Your help is much appreciated. – AP68 Jan 22 '21 at 19:27

2 Answers2

1

You can do the following:

  • Retrieve the desired range from Monthly, using Sheet.getRange.
  • Taking into account the source range number of rows via Range.getNumRows(), insert the corresponding number of blank rows to Daily (starting at row 4, first unfrozen one) using Sheet.insertRowsBefore.
  • Copy the retrieved range to Daily!B4 (first unfrozen row) using Range.copyTo.

Code snippet:

function copyRange() {
  const ss = SpreadsheetApp.getActive();
  const monthlySheet = ss.getSheetByName("Monthly");
  const dailySheet = ss.getSheetByName("Daily");
  const firstRow = 5;
  const sourceRange = monthlySheet.getRange(firstRow,2,monthlySheet.getLastRow()-firstRow+1,3);
  dailySheet.insertRowsBefore(4, sourceRange.getNumRows());
  const destRange = dailySheet.getRange("B4");
  sourceRange.copyTo(destRange,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
}
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Thanks Lamblichus. I don't want to delete anything, just insert. I'll put some code together which should indicate what I need and add it to my post. Many thanks. – AP68 Jan 22 '21 at 19:29
  • @AP68 If you don't want to delete anything, what do you want to do with the data that is in the `Daily` sheet *before* the copy process? Do you want to keep the old data, and append the new data below that? – Iamblichus Jan 22 '21 at 19:38
  • @lamblichus I need to keep what's already in the Daily sheet, and insert the data from the Monthly sheet above it. I'm going to add a code snippet to my question shortly. – AP68 Jan 22 '21 at 20:11
  • @AP68 Sorry I didn't understand your question before, thought you wanted to remove the existing rows, not shift them down. I updated my answer accordingly (I saw that you already posted an answer, but I think mine is more efficient, since it doesn't require having a cell to store the number of rows in the source range -`E2`- and it doesn't include all the `.activate()`, `getActiveRange()`, etc. methods, which are present in your script because you came up with it by recording a macro, but which are not necessary for the current purpose). I hope this is useful to you. – Iamblichus Jan 25 '21 at 09:48
  • @lamblichus Many thanks for taking the time to look at this. That works for me. Thanks. – AP68 Jan 28 '21 at 04:26
0

Having tried and failed to do this quite a few times, I've now managed to produce something that seems to work by recording a macro, working out what each line or section in the resulting script does, and editing it, with some additional online research required. It might be a bit clunky, but here's the result of my efforts:

function CommitMonthly() {
  // Script is only ever run from the Monthly sheet via the Commit button. Get the number of rows.
  var spreadsheet = SpreadsheetApp.getActive();
  var NumberOfRows = spreadsheet.getRange('E2').getValue();
  
  // Select the Daily sheet, select top row, insert required number of rows above it.
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Daily'), true);
  spreadsheet.getRange('B4').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), NumberOfRows);

  // Copy and paste the data from Monthly to new rows in Daily.
  spreadsheet.getRange('Monthly!B5:D').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Many thanks for your help.

AP68
  • 3
  • 3