Filling Data to last row or setting value of cells to last row in office script

[Youngdand]

Hi,

I have an office script that will be run though Power Automate. the automation extracts an email attachment, saves the file, then runs the office script, which deletes unnecessary images, header information and columns, and then needs to remove the last entire row, then change the values in the 4th column (D) to PT0006.

I have managed to perform all most of the steps, but am unable to get the last row and delete, and change the values in column 4 to paste, or set value to the last row of the sheet .

this is the script i have used:

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
let usedRange = selectedSheet.getUsedRange();
let lastRowAddress = usedRange.getLastRow().getRowIndex();
// Delete shapes
let sheets = workbook.getWorksheets();
for (let sheet of sheets) {
sheet.getShapes().forEach((shape, index) => {
if (shape.getType() === ExcelScript.ShapeType.image) {
shape.delete();
}
});
}
// Delete range 1:9 on selectedSheet
selectedSheet.getRange("1:9").delete(ExcelScript.DeleteShiftDirection.up);
// Delete range A:Fnon selectedSheet
selectedSheet.getRange("A:F").delete(ExcelScript.DeleteShiftDirection.left);
// Change Column titles
selectedSheet.getRange("A1:D1").setValues([["VRM", "PermitStartDate", "PermitEndDate", "PermitTypeRef"]]);
//selectedSheet.getRange("D2:D & (LastRowAddress)").setValue("PT0006");
selectedSheet.getRange("D2").setValue("PT0006");
selectedSheet.getRange("D2").autoFill("D2:D & (lastRowAddress)", ExcelScript.AutoFillType.fillCopy);
// Set range A1:D1 on sheet2
selectedSheet.getRange().getFormat().autofitColumns();
}


Thanks for any help you can give me.

Cheers,

Dan.

 

[Youngdand]

This has been solved now.

 

[Joe4]

Would you mind posting your solution?
If you do that, you can then also mark that post as the solution.

 

[Youngdand]

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let usedRange = selectedSheet.getUsedRange();
    let lastRowAddress = usedRange.getLastRow().getRowIndex();


    // Delete shape picture_1
    let sheets = workbook.getWorksheets();
    for (let sheet of sheets) {
        sheet.getShapes().forEach((shape, index) => {
            if (shape.getType() === ExcelScript.ShapeType.image) {
                shape.delete();
            }
        });
    }
    //Clear Formatting
    usedRange.clear(ExcelScript.ClearApplyTo.formats)
    //Delete Total Row
    selectedSheet.getRange(("A" + lastRowAddress) + ":J" + lastRowAddress).delete(ExcelScript.DeleteShiftDirection.up);
    // Delete range 1:9 on selectedSheet
    selectedSheet.getRange("1:9").delete(ExcelScript.DeleteShiftDirection.up);
    //Set Date Format
    selectedSheet.getRange("H:I").setNumberFormat("DD/MM/YYYY HH:MM:SS")
    //Delete Unnecessary Columns
    selectedSheet.getRange("A:F").delete(ExcelScript.DeleteShiftDirection.left);
    //Rename Columns
    selectedSheet.getRange("A1:D1").setValues([["VRM", "PermitStartDate", "PermitEndDate", "PermitTypeRef"]]);
  
    // Update the usedRange after deletion and get new last row adress
    usedRange = selectedSheet.getUsedRange();
    lastRowAddress = usedRange.getLastRow().getRowIndex();
    lastRowAddress = lastRowAddress+1
    // Add PetmitTypeRef to each Row
    selectedSheet.getRange("D2:D" + lastRowAddress).setValue("6");
  
}

 

[Joe4]

Thank you!