Is it possible to copy, paste, and delete data from one sheet to another in office scripts?-CodePudding

I currently have document saved on a share-point that documents all the testing that we do. Once the testing has been completed i would like to move the tests (entire row) to a separate sheet to essentially archive the test schedule (It would also need to paste the data in the next empty row going down), and because its now archived delete it from the submission sheet. Both sheets would be within the same workbook, and the number of rows would differ each time the script is run therefore the script code would need to work based on a selection input by the user (either prior to running the script or prompted by the script).

I've managed to do this in VBA using the following code, but i can't seem to locate a viable alternative to the selection function. Is there any way of essentially translating the below VBA code to Office script so the same thing happens in excel online?

    `Sub MoveCompletedTests()

     Selection.Copy Sheets("Archive - ATL staff only").Range("A1048576").End(xlUp).Offset(1, 0)

     Selection.Delete

     End Sub`

For reference i have a button on the "Sample submission" sheet that runs the above code on the selected range on that sheet moving it to the "Archive - ATL staff only" sheet.

I have attempted to use the script recorder but the script didn't allow for the selection made to be dynamic enough, it coded for the cell range rather than just selection.

I'd much appreciate any help provided.

Kind regards,

Ben

CodePudding user response：

I think that something like getSelectedRange should work (at least, if you are not working with several selected ranges at once):

    function main(workbook: ExcelScript.Workbook)
    {
      let destination: ExcelScript.Range;
      let source = workbook.getSelectedRange().getEntireRow();
      let archive = workbook.getWorksheet("Archive - ATL staff only");
      if(archive.getUsedRange() == undefined) {
        // if the archive sheet is empty, use the first row as a destination
        destination = archive.getRange("1:1");
      }
      else {
        // ... otherwise, use the next row after the last used
        destination = archive.getUsedRange().getRowsBelow().getEntireRow();
      }
      destination.copyFrom(source, ExcelScript.RangeCopyType.values);
      source.delete(ExcelScript.DeleteShiftDirection.up);
    }

p.s. To be clear, in case of VBA a Selection is a property of Application. But in case of ExcelScript it's a method of a Workbook object. This can be a bit confusing because there is an ExcelScript.Application interface, which has no mention of Selection.

CodePudding user response：

You can try the code here:

function main(workbook: ExcelScript.Workbook) {
    //Assign the source variable to the selected range
    let source: ExcelScript.Range = workbook.getSelectedRange()

    //Assign the archive variable to the Archive worksheet
    let archive: ExcelScript.Worksheet = workbook.getWorksheet("Archive - ATL staff only");

    //Set the destination range in the archive sheet
    let destination: ExcelScript.Range = archive.getRange("A:A").getExtendedRange(ExcelScript.KeyboardDirection.up).getLastCell().getOffsetRange(1,0)

    //Determine if the offset range is set to A2 in the Archive sheet.
    //If so, determine if A1 has a value. If it does not, update the
    //destination range to cell A1.

    if (destination.getAddress() === "'Archive - ATL staff only'!A2" ){
      if (destination.getOffsetRange(-1,0).getValue() === "") {
        destination = destination.getOffsetRange(-1,0)
      }
    }

    //Copy the selected source range to the destination range
    destination.copyFrom(source, ExcelScript.RangeCopyType.all);

    //Delete the data in the source range
    source.delete(ExcelScript.DeleteShiftDirection.up);
  }

This code determines the last cell based on the data in column A in the archive sheet. This can be useful if the used range of the archive sheet has data somewhere, but column A does not. In this scenario, this code would still update correctly.

Also, this code doesn't copy and delete the entire row. So if you intend to use a selection smaller than the entire row, this code may work better.