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

Question

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.

1. Please add a minimal reproducible example 2. Instead of adding "EDIT:" at the end fix your post. — Wicket
– Wicket, Commented Jan 25, 2022 at 4:32
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, when you modify "N24:BQ"+numOldEntries+24 to "N24:BQ"+(numOldEntries+24), what result will you obtain? If my guess was not useful, I apologize. — Tanaike
– Tanaike, Commented Jan 25, 2022 at 4:43
@Tanaike Thank you! That was the problem :) Please submit as an answer and I accept and upvote. To everyone else, I do apologize, I just wanted to keep it simple and included a snippet only. I don't ask a lot of questions here and, especially in my current state of frustration, I am prone to mistakes. I'll do better next time! — Brian - RGY Studio
– Brian - RGY Studio, Commented Jan 25, 2022 at 5:05
Thank you for replying. I'm glad your issue was resolved. From your replying, I posted it as an answer. Could you please confirm it? — Tanaike
– Tanaike, Commented Jan 25, 2022 at 5:09

Tanaike · Accepted Answer · 2022-01-25 05:08:36Z

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

Dharman · Accepted Answer · 2022-01-25 05:23:58Z

0

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

edited Jan 25, 2022 at 5:23

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jan 25, 2022 at 5:18

BLang44

11 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

From:

To:

Reference:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

From:

To:

Reference:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related