How can I write row(s) of 2d array to specific rows in Google Sheets?-CodePudding

I'm building a spreadsheet that should allow someone to add new entries but also enter past entries. I have a helper sheet that lets me code which entries are old ones that may have been edited and put them in an array. It could be 2d array (though for my tests, it's just one single row). However, setValues isn't working for various reasons. Here is the code snippet of concern:

  var newEntryStart = numOldEntries 24; //start row of new work entries
  var numNewEntries = numEntries - numOldEntries; //number of new work entries
  var oldEntries = helperSheet.getRange("N24:BQ" numOldEntries 24).getDisplayValues();
  var newEntries = helperSheet.getRange("N" newEntryStart ":BQ" numNewEntries 24).getDisplayValues();
  var dbSS = SpreadsheetApp.openById("1lOjK9QJulpeUZNBcnhj_b5tXBSoTpd_LbwgcUv8VOXM");
  var dbSheet = dbSS.getSheetByName("Database");
  var testVals2 = dbSheet.getRange("A1:A").getValues();
  var firstRow = testVals2.filter(String).length 1;
  var rowList = helperSheet.getRange("D3:D").getDisplayValues();
if (numOldEntries >0 ){
  for (i=0;i<numOldEntries;i  ){
    dbSheet.getRange(rowList[i],1,1,56).setValues([oldEntries[i]]);
  }
}

The error is in the setValues line at the end. I've tried various ways to turn the single row into a 2d array for setValues, but nothing works.

EDIT:
"rowList" contains the row numbers for each of the old entries in the master database, and second that each row of "oldEntries" contains 56 columns, most of which are blank, in case that is relevant to the issue I'm having. The usual error is that the number of rows doesn't match, or the number[] not valid for setValues error, things like that.

CodePudding user response：

When I saw your script, I thought that when numOldEntries is the number, numOldEntries 24 of "N24:BQ" numOldEntries 24 is used as the string. I'm worried that this might be related to your issue. If numOldEntries is the number, how about the following modification?

From:

var oldEntries = helperSheet.getRange("N24:BQ" numOldEntries 24).getDisplayValues();
var newEntries = helperSheet.getRange("N" newEntryStart ":BQ" numNewEntries 24).getDisplayValues();

To:

var oldEntries = helperSheet.getRange("N24:BQ" (numOldEntries 24)).getDisplayValues();
var newEntries = helperSheet.getRange("N" newEntryStart ":BQ" (numNewEntries 24)).getDisplayValues();

Or, you can also use the template literal as follows.

var oldEntries = helperSheet.getRange(`N24:BQ${numOldEntries 24}`).getDisplayValues();
var newEntries = helperSheet.getRange(`N${newEntryStart}:BQ${numNewEntries 24}`).getDisplayValues();

Reference:

Template literals

CodePudding user response：

Adding:

const formattedOldEntries = oldEntries.map( row => [row] );

before your for loop should give you an array containing a 2d array for each row.

Then you can use:

dbSheet.getRange(rowList[i],1,1,56).setValues(formattedOldEntries[i]);

inside your for loop and it should work. I can't say for certain though without seeing the sheet/being able to test it.

Are you trying to only update specific rows? If you are just trying to copy data from one sheet to another, you could skip the for loop and just use setValues to copy all of the data at once instead of line by line.

Documentation of Map Array Method