How to suspend execution until excel workbook 100% synced with Microsoft forms
I have a PowerShell script that’s extracting cell values from a table that’s synced to a Microsoft Form. However, when I run the script in the morning it’s extracting the cell values before the forms is 100% synced so I’m not getting all of the data from the day before.
I can sleep the script for maybe 20 seconds but is there a way to suspend execution of the script until the forms is 100% synced then immediately continue execution because sometimes it’s already synced and other times it’s not?
# create excel instance
$excel = new-object -comobject excel.application
# open excel workbook
try {
$workbook = $excel.Workbooks.Open($filePath, $true)
} catch {
throw “microsoft excel cannot access file -> please run the script again”
}
# set excel worksheet
$worksheet = $workbook.Sheets.Item(1)
# set excel table
$table = $worksheet.ListObjects.Item(“Form”)
# extract cell values
$values = @()
for ($i = 9; $i -le 15; $i++) {
foreach ($cell in $table.Range.Columns.Item($i).SpecialCells(12).Cells) {
$values += $cell.Value2 | Where-Object { $_ -match “^d” }
}
}
I have a PowerShell script that’s extracting cell values from a table that’s synced to a Microsoft Form. However, when I run the script in the morning it’s extracting the cell values before the forms is 100% synced so I’m not getting all of the data from the day before. I can sleep the script for maybe 20 seconds but is there a way to suspend execution of the script until the forms is 100% synced then immediately continue execution because sometimes it’s already synced and other times it’s not? # create excel instance
$excel = new-object -comobject excel.application
# open excel workbook
try {
$workbook = $excel.Workbooks.Open($filePath, $true)
} catch {
throw “microsoft excel cannot access file -> please run the script again”
}
# set excel worksheet
$worksheet = $workbook.Sheets.Item(1)
# set excel table
$table = $worksheet.ListObjects.Item(“Form”)
# extract cell values
$values = @()
for ($i = 9; $i -le 15; $i++) {
foreach ($cell in $table.Range.Columns.Item($i).SpecialCells(12).Cells) {
$values += $cell.Value2 | Where-Object { $_ -match “^d” }
}
} Read More
