Forum Discussion
Solved
auto fill in office script api
Hi, I am trying to auto fill a row of data that changes every month. right now the script is as below. I have bolded where I need help. the auto fill range is set to go to d11203 but the script will ...
If I understood correctly initially you have some data in column D. You shift it to the right, applied some formulae and formats and autofill D on the size of initial data. If so that could be
function main(workbook: ExcelScript.Workbook) { const sheet = workbook .getActiveWorksheet(); const dataSize = sheet .getRange("D:D") .getUsedRange() .getRowCount() -1 sheet .getRange("D:D") .insert(ExcelScript.InsertShiftDirection.right) sheet .getRange("D1:D2") .setFormulasLocal([["Duration_Minutes"], ["=(E2/60)"]]) sheet .getRange("D:E") .setNumberFormatLocal("General") sheet .getRange("D2") .setNumberFormatLocal("0.00") sheet .getRange("D2") .autoFill( sheet .getRange("D2") .getResizedRange(dataSize - 1, 0) , ExcelScript.AutoFillType.fillDefault) }
If I understood correctly initially you have some data in column D. You shift it to the right, applied some formulae and formats and autofill D on the size of initial data. If so that could be
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook
.getActiveWorksheet();
const dataSize = sheet
.getRange("D:D")
.getUsedRange()
.getRowCount() -1
sheet
.getRange("D:D")
.insert(ExcelScript.InsertShiftDirection.right)
sheet
.getRange("D1:D2")
.setFormulasLocal([["Duration_Minutes"], ["=(E2/60)"]])
sheet
.getRange("D:E")
.setNumberFormatLocal("General")
sheet
.getRange("D2")
.setNumberFormatLocal("0.00")
sheet
.getRange("D2")
.autoFill(
sheet
.getRange("D2")
.getResizedRange(dataSize - 1, 0)
, ExcelScript.AutoFillType.fillDefault)
} 
Thank you so much that works perfectly Sergei! That works perfectly thank you so much Sergei. I also have many cells that have a zero in them in column D and I want to delete the entire row if there is a 0 in column D. I have found a VBA for this but there's much in terms of excel script to do it and I need it to be a office script and I can't do a filter and delete because then it deletes the column header which I need for the data. could you help with this? this is what I have this so far but it just returns errors and the other one I had was just deleting the entire workbook. Thank you so much for your help!
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook
.getActiveWorksheet();
const dataSize = sheet
.getRange("D:D")
.getUsedRange()
.getRowCount() - 1
let selectedSheet = workbook.getActiveWorksheet();
// Toggle auto filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getRange());
// Apply values filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 4, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
// Delete all cells on selectedSheet
selectedSheet
.getRange("D2")
.delete(
.getRange(dataSize - 1, 0)
, ExcelScript.Delete.up)
}
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook
.getActiveWorksheet();
const dataSize = sheet
.getRange("D:D")
.getUsedRange()
.getRowCount() - 1
let selectedSheet = workbook.getActiveWorksheet();
// Toggle auto filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getRange());
// Apply values filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 4, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
// Delete all cells on selectedSheet
selectedSheet
.getRange("D2")
.delete(
.getRange(dataSize - 1, 0)
, ExcelScript.Delete.up)
}
If to remove first rows with zero (not blank or empty string) in column D and after that add durations as before, that could be
function main(workbook: ExcelScript.Workbook) { const sheet = workbook .getActiveWorksheet(); //here we remove rows with 0 in column D const range = sheet .getRange("D:D") .getUsedRange() const values = sheet .getRange("D:D") .getUsedRange() .getValues() let deleteRows: number[] = []; let k: number = 0; values.forEach( v => { if ( v[0] == 0) { deleteRows.push(k); } k++; }) for (let n = deleteRows.length-1; n > -1; n--) { range .getCell(deleteRows[n], 0) .getEntireRow().delete(ExcelScript.DeleteShiftDirection.up); } // next shift columns to the right // and insert durations const dataSize = sheet .getRange("D:D") .getUsedRange() .getRowCount() -1 sheet .getRange("D:D") .insert(ExcelScript.InsertShiftDirection.right) sheet .getRange("D1:D2") .setFormulasLocal([["Duration_Minutes"], ["=(E2/60)"]]) sheet .getRange("D:E") .setNumberFormatLocal("General") sheet .getRange("D2") .setNumberFormatLocal("0.00") sheet .getRange("D2") .autoFill( sheet .getRange("D2") .getResizedRange(dataSize - 1, 0) , ExcelScript.AutoFillType.fillDefault) }- You are truly God sent! thank you so much Sergei, I couldn't have completed this project without you. Thank you.
blairwebb , you are welcome, glad to help
