I'm encountering an issue with a script that is working fine, except for the part where it's supposed to clear the B4:B120 area in the "// Clear the Margin Updates column" section - which is mysteriously greyed out:
function main(workbook: ExcelScript.Workbook): ReportImages {
// Make sure all tables and charts are updated by recalculating the workbook.
workbook.getApplication().calculate(ExcelScript.CalculationType.full);
// Grab data from the "Target Margins - FHM" table. (name of Excel tab, not name of table)
let sheet1 = workbook.getWorksheet("Sheet1");
const table = workbook.getWorksheet('Target Margins - FHM').getTables()[0];
const rows = table.getRange().getTexts();
// Select only Product Type and Margin Update columns, excluding the Total row.
const selectColumns = rows.map((row) => {
return [row[0], row[1]];
});
// Remove existing "ChartSheet" worksheet if present, then add a new one.
workbook.getWorksheet('ChartSheet')?.delete();
const chartSheet = workbook.addWorksheet('ChartSheet');
// Add selected data to the new worksheet.
const targetRange = chartSheet.getRange('A1').getResizedRange(selectColumns.length - 1, selectColumns[0].length - 1);
targetRange.setValues(selectColumns);
// Get images of the chart and table, and return them for use in a Power Automate flow.
const tableImage = table.getRange().getImage();
return { tableImage };
// The goal here is to clear the Margin Updates column.
const targetSheet = workbook.getActiveWorksheet();
const getRange = targetSheet.getRange("B4:B120");
getRange.clear(ExcelScript.ClearApplyTo.contents);`
}
// Interface for table and chart images.
interface ReportImages {
tableImage: string
}
This code copies data from sections of the A and B columns into a table, then sends an email via a Power Automate flow. However, I am stuck on clearing the values in the B column after this process.
Your assistance would be greatly appreciated.
Thank you.
@cybernetic.nomad:
When attempting Range ("B4:B120").Clear, I encounter:
unreachable code detected (7027)
and
"cannot find name 'Range' (2304)