How to automatically transfer data between two worksheets using Office Scripts based on user input

[jeff88]

Hello,

I am trying to setup a worksheet with two sheets, one that has a "Jobs" sheet and one with a "Completed Jobs" sheet. On the "Jobs" sheet I will have rows of data and the column at the end will have a dropdown menu with a "Complete" option. I want a user to be able to select the "Complete" option and Excel automatically moves that row over to the "Completed Jobs" sheet.

I am using the web app as this is for multiple users so I need to use Office Scripts.

@skillilea got me started with some basic code (thank you skillilea) but I need to figure out the rest of the code to get it to work

function main(workbook: ExcelScript.Workbook) {
  let sourceSheet = workbook.getWorksheet("Sheet1");
  let targetSheet = workbook.getWorksheet("Sheet2");
 
  let sourceRange = sourceSheet.getRange("A1:C10");
  let valuesToTransfer = sourceRange.getValues();
 
  let targetRange = targetSheet.getRange("A1");
 
  targetRange.setValues(valuesToTransfer);
}

 

[jeff88]

Bumping this as I'm trying to get this to work still. Does anybody know Office Scripts that can assist in the code I would need to make it work?

 

[nguyendang]

Hello,

I am trying to setup a worksheet with two sheets, one that has a "Jobs" sheet and one with a "Completed Jobs" sheet. On the "Jobs" sheet I will have rows of data and the column at the end will have a dropdown menu with a "Complete" option. I want a user to be able to select the "Complete" option and Excel automatically moves that row over to the "Completed Jobs" sheet.

I am using the web app as this is for multiple users so I need to use Office Scripts.

@skillilea got me started with some basic code (thank you skillilea) but I need to figure out the rest of the code to get it to work

function main(workbook: ExcelScript.Workbook) {
  let sourceSheet = workbook.getWorksheet("Sheet1");
  let targetSheet = workbook.getWorksheet("Sheet2");
 
  let sourceRange = sourceSheet.getRange("A1:C10");
  let valuesToTransfer = sourceRange.getValues();
 
  let targetRange = targetSheet.getRange("A1");
 
  targetRange.setValues(valuesToTransfer);
}

Hello,
I have read your previous post with the sample data you provided, so that I suggest the following script:

function main(workbook: ExcelScript.Workbook) {
  const sourceWs = workbook.getWorksheet("Jobs");
  const targetWs = workbook.getWorksheet("Completed Jobs");
  if (sourceWs && targetWs) {
    let lastRow = sourceWs.getUsedRange().getRowCount();
    const range = sourceWs.getRange(`A1:E${lastRow}`);
    const result = doWork(range);
    if (result.length > 0) {
      const targetWsLastRow = targetWs.getUsedRange().getRowCount();
      const rowCount = result.length;
      const columnCount = result[0].length;
      const destinationRange = targetWs.getRangeByIndexes(targetWsLastRow, 0, rowCount, columnCount);
      destinationRange.setValues(result);
    }
  }
}

function doWork(range: ExcelScript.Range): (string | number | boolean)[][] {
  const values = range.getValues();
  const rowCount = values.length;
  const columnCount = values[0].length;
  const result: (string | number | boolean)[][] = [];
  for (let i = rowCount -1; i > 0; i--) {
    const row = values[i];
    if (row[4] == "Yes") {
      result.push(row.slice(0, -1));
      const rowRange = range.getRow(i);
      rowRange.delete(ExcelScript.DeleteShiftDirection.up);
    }
  }
  return result;
}

Before running script,

After running script,

Remember to modify the script to suit your need.
Hope this will help.