Script to copy data from set of cells to another set of cells

Question

I'm running a script which automatically generates values for the A and B columns, and then I want to copy the values generated through the E-I columns (columns C and D run formulas to print out values of E-I), and copy them into J-N (the initial values reload every time I open the sheet, so I'd like to just copy their value instead of using =E2 for example). This is what I'm working with so far:

function daily() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Instagram");
var date = Utilities.formatDate(new Date(), "GMT", "YYYY-MM-DD");
var aValues = sh.getRange("A1:A").getValues();
var aLast = aValues.filter(String).length;

// Assuming A and B have same last row, no need for B
// If different, need to calculate separately
var bValues = sh.getRange("B1:B").getValues();
var bLast = bValues.filter(String).length;

// If A and B are the same, use setValues
sh.getRange(aLast + 1, 1, 1, 2).setValues([[date,'microdosehq']]);

var sheet = SpreadsheetApp.getActiveSheet();
var balance = sheet.getRange("E1:E").getValues();
var nextRow = getFirstEmptyRow('J');
// Record current balance and timestamp at end of columns B & C
sheet.getRange(nextRow, 10, 1, 1).setValues([[balance]]);

}

// From https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRow(columnLetter) {
columnLetter = columnLetter || 'A';
var rangeA1 = columnLetter + ':' + columnLetter;
var spr = SpreadsheetApp.getActiveSpreadsheet();
var column = spr.getRange(rangeA1);
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct][0] != "" ) {
ct++;
}
return (ct+1); // +1 for compatibility with spreadsheet functions
}

But it's always returning the value of E1 into the next available J cell, and right now I'm not focused on all the columns, just column E. I've added an image here to show what I'd like to accomplish at the end of the day.

Any extra support would be much appreciated!

I don't see the relation between your image and your explanation since you image does not include column letters. However I would recommend not using ranges like this `sheet.getRange("E1:E").getValues();` us this instead `sheet.getRange(1,5,sheet.getLastRow()).getValues();` because the form will return nulls from lastRow() down to maxRows() — Cooper, Jan 15 '21 at 17:11

NightEye · Accepted Answer · 2021-01-15T18:04:01.583

I have simplified your code due to having problems during testing.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Instagram");

function daily() {
  var date = Utilities.formatDate(new Date(), "GMT", "YYYY-MM-DD");

  // Created a function that returns last row of column
  // Add 1 to get first row of column which does not have a value
  var aNext = getLastRow('A') + 1; 
  sh.getRange(aNext, 1, 1, 2).setValues([[date, 'microdosehq']]);

  // Assuming E-I columns have the same number of rows
  // Get last row of I which does have a value
  var iLast = getLastRow('I');
  // Excluded to get the header, if needed, change to E1
  var balance = sh.getRange("E2:I" + iLast).getDisplayValues();

  // Get first row of J which does not have a value
  var jNext = getLastRow('J') + 1;
  // Range of J-N starting from first blank J row
  var jNRange = "J" + jNext + ":N" + (jNext + balance.length - 1);

  // Copy values to J-N
  sh.getRange(jNRange).setValues(balance);
}

function getLastRow(column){
  var range = column + "1:" + column;
  var values = sh.getRange(range).getValues();
  return values.filter(String).length;
}

In short, the code above copies the rows under E-I, and are being appended under J-N, like what your snippet here shows:

var balance = sheet.getRange("E1:E").getValues();
var nextRow = getFirstEmptyRow('J');
// Although the line below doesn't append
sheet.getRange(nextRow, 10, 1, 1).setValues([[balance]]);

I removed everything that were unrelated to the question to show a clear answer, so please re-add them if needed.

Sample Data:

1st run:

2nd run:

If I misunderstood your question and this was not the result you want, I apologize. Feel free to clarify below if this was not the result you want.

Script to copy data from set of cells to another set of cells

1 Answers1