Scripting Help Needed. "Moving Rows from one sheet to another"

Question

Good morning,

I am working thru a problem and I cannot use VBA as that will disallow autosave to be on (which our company requires on this specific document).

I have two tables on two separate sheets. I would like to MOVE completed jobs from "Job" table to "CompletedJobs" table.

The column for criteria of Complete, which will simple have a "Y" is U:U.

This issue is causing me lots of problems and I just cannot figure it out. Can anyone help?

Having any set filters within the script is not an absolute requirement.

The script that i have input is not returning any rows. Not sure what needs to be adjusted.

https://drive.google.com/drive/folders/11JhtjSVmXZ7WeKEc4GTfCRJL-wtAPgzV?usp=share_link

---

function main(workbook: ExcelScript.Workbook) {

// Update the table names, column index to look-up on as neededconst TargetTableName = 'Job';const SourceTableName = 'CompletedJobs';const IndexOfColumnToFilterOn = 19; // 0-indexconst NameOfColumnToFilterOn = 'Complete';const ValueToFilterOn = 'Y';

// Get the table objectslet targetTable = workbook.getTable(TargetTableName);let sourceTable = workbook.getTable(SourceTableName);

// If you don't know the table names, you can fetch first table on a given worksheet name// let targetTable = workbook.getWorksheet('Table test').getTables()[0];// let sourceTable = workbook.getWorksheet('Completed Jobs').getTables()[0];

if (!targetTable || !sourceTable) {console.log(Tables missing - Check to make sure both source (${TargetTableName}) and target table (${SourceTableName}) are present before running the script. );return;}

// Save all of the filter criteria// Initialize an empty object to hold the filter criteriaconst tableFilters = {};// For each table column, collect the filter criteriasourceTable.getColumns().forEach((column) => {let colFilterCriteria = column.getFilter().getCriteria();if (colFilterCriteria) {// If we don't remove these two keys, the API fails for some reason. So, remove these..delete colFilterCriteria['@odata.type'];delete colFilterCriteria['subField'];tableFilters[column.getName()] = colFilterCriteria;}});

// Range object of table dataconst sourceRange = sourceTable.getRangeBetweenHeaderAndTotal();// Get data values of the tablw rowsconst dataRows: (number | string | boolean)[][] = sourceTable.getRangeBetweenHeaderAndTotal().getValues();

// Create variables to hold the rows to be moved and their addresseslet rowsToMoveValues: (number | string | boolean)[][] = [];let rowAddressToRemove: string[] = [];

// Get the data values to insert to target tablefor (let i = 19; i < dataRows.length; i++) {if (dataRows[i][IndexOfColumnToFilterOn] === ValueToFilterOn) {rowsToMoveValues.push(dataRows[i]);// Get the intersection between table address and the entire row where we found the match. This provides the address of the range to remove.let address = sourceRange.getIntersection(sourceRange.getCell(i, 19).getEntireRow()).getAddress();rowAddressToRemove.push(address);}}// If no data rows to process, exit script.if (rowsToMoveValues.length < 1) {console.log('No rows selected from the source table that matched the filter criteria.');return;}console.log(Adding ${rowsToMoveValues.length} rows to target table.);// Insert rows at the end of target table. Change the first argument to suit your target location (e.g., 0 for beginning, -1 for end).targetTable.addRows(-1, rowsToMoveValues)// Get worksheet reference where the table rows to be deleted resides.const sheet = sourceTable.getWorksheet();

// Remove all filters before removing rowssourceTable.getAutoFilter().clearCriteria();

// !!Important!!  Reverse the address and remove from the bottom so that the right rows are removed. If not reversed, the resulting row upwards shift will mean that incorrect rows will be removed.console.log(Removing ${rowAddressToRemove.length} from the source table. )rowAddressToRemove.reverse().forEach((address) => {sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);});// Re-apply filters// Log the criteria for testing purpose (not required)console.log(tableFilters);// Format source table to wrap text (important to do this before applying back filters)sourceTable.getRange().getFormat().setWrapText(true);

// Re-apply all column filtersreApplyFilters(sourceTable, NameOfColumnToFilterOn, tableFilters);console.log("Finished.")return;}

function reApplyFilters(sourceTable: ExcelScript.Table, columnNameFilteredOn: string, tableFilters: {}): void {

// Re-apply all column filtersObject.keys(tableFilters).forEach((columnName) => {sourceTable.getColumnByName(columnName).getFilter().apply(tableFilters[columnName]);});sourceTable.getColumnByName(columnNameFilteredOn).getFilter().clear();return;}

Answer 1

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

It looks like you are using TypeScript in your code, which is a programming language that extends JavaScript with optional type annotations. TypeScript is designed to help developers catch type errors at compile time, rather than runtime, by requiring explicit type declarations for variables.

To fix the errors you are seeing, you can add explicit type declarations for the variables in your code. For example, you can add the type declarations like this:

let completeColumn: Excel.Range = sourceTable.getColumn("Complete"); let visibleRows: Excel.Range[] = sourceTable.getRange().getRows().filter((row: Excel.Range) => row.getHidden() === false); let rowsToMoveValues: any[][] = visibleRows.map((row: Excel.Range) => row.getValues()); let rowAddressesToRemove: string[] = visibleRows.map((row: Excel.Range) => row.getAddress()); let sourceWorksheet: Excel.Worksheet = sourceTable.getWorksheet();

In this example, I have added type declarations for the variables completeColumn, visibleRows, rowsToMoveValues, rowAddressesToRemove, and sourceWorksheet.

Note: The type Excel.Range represents a range of cells in an Excel worksheet, and the type Excel.Worksheet represents a worksheet in an Excel file.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Answer 2

Thank you again.

I know this may be a silly question... but do I need to specific the worksheets / columns / data ranges any where in the script?

Here is the script I have plugged in:

// Get the "Complete" column in the source table let completeColumn: Excel.Range = sourceTable.getColumn("Complete");

// Filter the rows in the source table based on the value "Y" in the "Complete" columncompleteColumn.filter("Y");

// Get the range of rows in the source table that are currently visible (i.e., not filtered out)let visibleRows: Excel.Range[] = sourceTable.getRange().getRows().filter((row) => row.getHidden() === false);

// Get the values of the visible rows in the source tablelet rowsToMoveValues: any[][] = visibleRows.map((row) => row.getValues());

// Get the addresses of the visible rows in the source tablelet rowAddressesToRemove: string[] = visibleRows.map((row) => row.getAddress());

// Insert the values of the visible rows in the target tabletargetTable.addRows(-1, rowsToMoveValues);

// Get the worksheet where the source table is locatedlet sourceWorksheet: Excel.Worksheet = sourceTable.getWorksheet();

// Remove the rows in the source table that are currently visible (i.e., not filtered out)sourceWorksheet.getRange(rowAddressesToRemove.join(",")).clear();

// Remove the filter from the "Complete" column

---

These are the errors now returning. Again so sorry to be a pain

See line 11, column 23: Explicit Any is not allowed

See line 2, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 20, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 8, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

Answer 3

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

To fix this error, you need to explicitly declare the data type of each variable by adding a colon (:) and the data type after the variable name. For example:

let completeColumn: Excel.Range = sourceTable.getColumn("Complete");

let visibleRows: Excel.Range[] = sourceTable.getRange().getRows().filter((row) => row.getHidden() === false);

let rowsToMoveValues: any[][] = visibleRows.map((row) => row.getValues());

let rowAddressesToRemove: string[] = visibleRows.map((row) => row.getAddress());

let sourceWorksheet: Excel.Worksheet = sourceTable.getWorksheet();

After declaring the data type of each variable, the script should be able to run without any errors.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Answer 4

Thank you!

I tried inputting the script (I mad no adjustments, please let me know if i should).

It returned this in the output pane once I ran the script. This is my first time delving into scripts, thank you for your patience!

See line 2, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 8, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 11, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 14, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 20, column 5: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

Answer 5

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

To filter the rows in the source table based on the value in the "Complete" column, you can use the filter function in ExcelScript. Here's an example of how you can use it:

// Get the "Complete" column in the source table let completeColumn = sourceTable.getColumn("Complete");

// Filter the rows in the source table based on the value "Y" in the "Complete" column completeColumn.filter("Y");

// Get the range of rows in the source table that are currently visible (i.e., not filtered out) let visibleRows = sourceTable.getRange().getRows().filter((row) => row.getHidden() === false);

// Get the values of the visible rows in the source table let rowsToMoveValues = visibleRows.map((row) => row.getValues());

// Get the addresses of the visible rows in the source table let rowAddressesToRemove = visibleRows.map((row) => row.getAddress());

// Insert the values of the visible rows in the target table targetTable.addRows(-1, rowsToMoveValues);

// Get the worksheet where the source table is located let sourceWorksheet = sourceTable.getWorksheet();

// Remove the rows in the source table that are currently visible (i.e., not filtered out) sourceWorksheet.getRange(rowAddressesToRemove.join(",")).clear();

// Remove the filter from the "Complete" column completeColumn.clearFilter();

This script filters the rows in the source table based on the value "Y" in the "Complete" column, gets the values and addresses of the visible rows, inserts the values in the target table, removes the rows from the source table, and then removes the filter from the "Complete" column.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha