Search code examples
google-apps-scriptcopy-paste

Copy data range but ignore (not copy) cells with formula and paste to last row in another sheet. Below script was working, but now

I was using the following script in order to copy data from one sheet to another in the same spreadsheet with values only and in a specific cell.

My problem is that now the script is not correctly working. When I check the sheet where the values are copied, there has been made error like "Spreadsheets". I don't know what is exactly wrong and how to fix that code. Maybe it is on the last row of scripts.

I would really appreaciate your help. Thank you!!

function Movedata1() {
      var ss = SpreadsheetApp.getActive();
      var sh = ss.getSheetByName("Receipt");
      var range = sh.getRange("B12:W44");
      var values = range.getValues();
      var dsh = ss.getSheetByName("Historical Receipts");
      dsh.getRange(dsh.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
      };
Solution

  • From your following reply,

    I made mistake that how images are exported. Image's formula is being like image(), and error is connected that.

    From this, I thought that the reason for your issue is due to the inner image in a cell. In this case, how about using copyTo instead of setValues? The modified script is as follows.

    Modified script:

    function Movedata1() {
  var ss = SpreadsheetApp.getActive();
  var srcSheet = ss.getSheetByName("Receipt");
  var dstSheet = ss.getSheetByName("Historical Receipts");
  var srcRange = srcSheet.getRange("B12:W44");
  var dstRange = dstSheet.getRange(dstSheet.getLastRow() + 1, 1);
  srcRange.copyTo(dstRange); // or srcRange.copyTo(dstRange, { contentsOnly: true });
}

    References: