Simple Google Spreadsheet Script to loop copy and paste cells

Question

So this is most likely the simplest question but I am just getting to learn this myself. Sorry for the silly question.

I have a huge Google Spreadsheet (2700+ rows) I need to loop and automate the move of data from one cell (dataA) to another (in front of the next row of data starting with dataB), then remove the 3 rows above the data row and move to the next record (block of 4 rows). Before I do anything I have to make room by inserting a column.

I have created a function that inserts the column, copies the first data cell (now B2) to its new destination (A4) and then deletes the useless three rows above the destination correctly. Because this is relative and iterative, I do not know how to adapt this part with a loop that will go through the rest of the entire sheet.

How do I create a loop that moves through each block of 4 rows, performs the actions mentioned, then moves on to the next block of 4 rows leaving each completed row one after another at the top?

This is an example of the starting data structure:

starting data structure - one record of many

function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();

  var sheet = ss.getSheets()[0];

  // This inserts a column in the first column position
  sheet.insertColumnBefore(1);

  var start = ss.getRange("B2");

  var source = ss.getRange("B2");
  source.copyTo (ss.getRange ("A4"), {contentsOnly: true});
  source.clear ();
  sheet.deleteRows(1, 3);
}

I would write a function to do this. You'll want to use row, column values for getRange instead of A1 notation. Pass in the sheet as a parameter to the function and also pass in the source row/column and destination row/column as parameters. Then loop over your rows and call this function incrementing rows/columns as necessary. Also could be faster to just create a new sheet rather than deleting old rows. — User
I understand how it works theoretically, the syntax itself is where I struggle. — Peter Sinkevich

Hugo Nakamura Hugo Nakamura · Accepted Answer · 2016-04-26T01:25:16

To create a loop you first need to know where you should start, when you stop and how fast you progress.

You start on the first row, go until the last one which you can get using getLastRow() and you progress 4 rows in one step.

I suggest that you adapt moveValuesOnly() to accept an input rowstart and use the loop to call it several times but with different row values. Note that you will have to change you currently hardcoded input of "B2" and "A4" to something based on the rowstart value. The getDataRange().getValues(); might be useful for you to edit your sheet.

The loop would look like

for (i=0; i<sheet.getLastRow(); i+=4) {
  moveValuesOnly(i);
}

Simple Google Spreadsheet Script to loop copy and paste cells

1 Answers