0

I want to paste array values in desired cells. But I am getting errors in line 23.

function main(workbook: ExcelScript.Workbook) 
{
  let emp_sheet = workbook.getWorksheet("Emp");
  let cc_id_range = emp_sheet.getRange("F1:O17");
  let cc_id_values = cc_id_range.getValues();

  for (let k = 1; k <= 10; k++) 
  {
    let cc_id = cc_id_values[0][k - 1];
    var b = Number(cc_id); // For example here I get b = 26

    if (b != 0) 
    {    
      for (let m = 1; m <= 13; m++) 
      {
        let cc_data = cc_id_values[m + 3][k - 1];
        var c = Number(cc_data);
        console.log(c)

        let output_sheet = workbook.getWorksheet("Output_CC");
        let output_range = output_sheet.getRange("F2:BM14");

        output_range.getCell[m - 1][b-1].setValues(c);   // Getting Error here
      }
    }
  }
}

Please see this image Code

kartrabby
  • 1
  • 2
  • 1
    Your introductory sentence does not make sense in a question. If you want to directly respond to an answer, then post a comment on that answer. If you want to update your question in response to an answer, make sure the question still makes sense as a question. – Mark Rotteveel Jul 09 '21 at 13:17

2 Answers2

2

I believe what you are trying to accomplish is copy a table from one tab to another tab. Please see the following screenshots and code below. I have it pasting in different ranges so you can understand how to tweak it.

function main(workbook: ExcelScript.Workbook) {
  let wrkSh1 = workbook.getWorksheet("Sheet1");
  let wrkSh2 = workbook.getWorksheet("Sheet2");
  let lastRow = wrkSh1.getRange("A:A").getUsedRange(true).getLastCell().getRowIndex();
  let newRng = "A1:C" + (lastRow + 1);
  let copyVal = wrkSh1.getRange(newRng).getValues();
  newRng = "C3:E" + (lastRow + 3);
  wrkSh2.getRange(newRng).setValues(copyVal);
}

enter image description here

enter image description here

Ethan
  • 808
  • 3
  • 21
  • 1
    Hi Ethan. Thank you for responding. I believe you are just copying data and pasting it in the desired range. But I am storing a value in Array and I want that array value to be pasted in desired cells. Please look at the code image. I modified my question for better understanding. – kartrabby Jul 09 '21 at 13:13
  • It didn’t help much, can you attach a screenshot of Emp – Ethan Jul 09 '21 at 21:34
0

You need to call the getCell method with the row and column as parameters, not indices. Try this:

output_range.getCell(m - 1, b-1).setValues(c);

Your version is treating the function itself like an array, which is causing problems.

Also, you might find the Range.copyTo method useful here. It basically works like copy/paste.

Alexander Jerabek
  • 358
  • 1
  • 3
  • 10
  • Hello Alexander, It's working perfectly fine now. Thank you. I have one more question. Please look into my new question. I am using a loop to get a range of cells. But when I specify range, it's returning a string. Please help me with this. Also, I have attached an image of the Error. Please help me with this. – kartrabby Jul 10 '21 at 16:06
  • Looks like your other question was answered. Do you still need assistance? If the answers provided are helpful, please "accept" them on Stack Overflow so other users with the same problem can see the solution (and other users know you've already been helped). Thanks! – Alexander Jerabek Jul 12 '21 at 16:42