How can I get a cell in a cell and move into another file?

I'm trying something along below lines, like a dizzy cockroach, but unsuccessfull so far:

function moveOrders () {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const orderSheet = ss.getSheetByName('Orders');
const activeSheetName = e.source.getActiveSheet().getName();
const thisRow = e.range.getRow();
const thisCol = e.range.getColumn();
const cellValue = e.range.getValue();
const destinationSheet = ss.getSheetByName('Sheet31');

if (activeSheetName == orderSheet.getName() && thisRow > 5 && thisCol < 13 && cellValue == 'Confirmed') {
    const rowValues = orderSheet.getRange(thisRow, 1, 1, 13).getValues();
    const image = SpreadsheetApp.newCellImage(rowValues[2]).setAltTextDescription('Clothing Piece').toBuilder().build();
    destinationSheet.getRange(destinationSheet 1,1,rowValues.length, rowValues[0].length).setValues(rowValues)
}

This is how I get the value, the image being highlighted in green (3rd element of the array:

It throws the following error:

Parametes null don't correspond to method signatures for SpreadsheetApp.newCellImage

Here's a sample file for tests: https://docs.google.com/spreadsheets/d/1gxhXeNCMZ8MnJds1LM7vGQqibYgfXDYPkA6q6VNHcN0/edit?usp=sharing

CodePudding user response：

I thought that in your situation, this thread might be useful. Ref And, if this is reflected in your script, how about the following modification?

And, from const activeSheetName = e.source.getActiveSheet().getName();, const thisRow = e.range.getRow(); and so on, I guessed that you might want to use your script as the OnEdit trigger.

Modified script:

function onEdit(e) {
  const orderSheet = e.source.getSheetByName('Orders');
  const activeSheetName = e.source.getActiveSheet().getName();
  const thisRow = e.range.getRow();
  const thisCol = e.range.getColumn();
  const cellValue = e.range.getValue();
  const destinationSheet = e.source.getSheetByName('Sheet31');
  if (activeSheetName == orderSheet.getName() && thisRow > 5 && thisCol < 13 && cellValue == 'Confirmed') {
    const rowValues = orderSheet.getRange(thisRow, 1, 1, 13);
    rowValues.copyTo(destinationSheet.getRange(destinationSheet.getLastRow()   1, 1), { contentsOnly: true });
  }
}

• In your showing script, destinationSheet of destinationSheet 1 is Sheet object. In this case, an error occurs. I guessed that you might use destinationSheet.getLastRow() 1.

• When this script is run, the row is copied from "Orders" to "Sheet31" using copyTo. By this, the row including both the image and the values can be copied.

References:

• Related thread.
  • Moving contents of a cell with app script in Google sheets
• copyTo(destination)

Added

About Does this work between files, as well?, when the above script is modified, it becomes as follows. In this case, please install the OnEdit trigger to installedOnEdit.

Sample script:

function installedOnEdit(e) {
  const dstSpreadsheetId = "###"; // Please set the destination Spreadsheet ID.

  const orderSheet = e.source.getSheetByName('Orders');
  const activeSheetName = e.source.getActiveSheet().getName();
  const thisRow = e.range.getRow();
  const thisCol = e.range.getColumn();
  const cellValue = e.range.getValue();
  if (activeSheetName == orderSheet.getName() && thisRow > 5 && thisCol < 13 && cellValue == 'Confirmed') {
    const dstSS = SpreadsheetApp.openById(dstSpreadsheetId);
    const destinationSheet = dstSS.getSheetByName('Sheet31');
    const temp = orderSheet.copyTo(dstSS);
    const rowValues = temp.getRange(thisRow, 1, 1, 13);
    rowValues.copyTo(destinationSheet.getRange(destinationSheet.getLastRow()   1, 1), { contentsOnly: true });
    dstSS.deleteSheet(temp);
  }
}

CodePudding user response：

You can just use moveTo(), there's no need for getValues and setValues this will cut and paste both format and value from this range and target range.

Try this code instead:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const orderSheet = ss.getSheetByName('Orders');
  const activeSheetName = e.source.getActiveSheet().getName();
  const thisRow = e.range.getRow();
  const thisCol = e.range.getColumn();
  const cellValue = e.range.getValue();
  const destinationSheet = e.source.getSheetByName('Destination');
  if (activeSheetName == orderSheet.getName() && thisRow > 5 && thisCol < 13 && cellValue == 'Confirmed') {
    const rowValues = orderSheet.getRange(thisRow, 1, 1, 13);
    rowValues.moveTo(destinationSheet.getRange(destinationSheet.getLastRow()   1, 1));
  }
}

Let me know if this works!

Reference: https://developers.google.com/apps-script/reference/spreadsheet/range#movetotarget