Working with excel rows and columns

Hi everyone,

If anybody can help with providing a solution to this (hopefully simple) request.

I am able to read text on the SUT and determine if it is in a column of a resource (xlsx). What I cannot figure out though is this for example;

The keyword I have found using readtext is also located in my resource excel file (xlsx) at column A row 4. I would then like to reply to that word using typetext but with the word adjacent to it in the excel file such as column B row 4.

So basically im looking for a word on the screen that is within my spreadsheet in column A, and then reply with the corresponding word in the row but on column B.


How about using SenseTalk’s built in function to read Excel spreadsheet?

first find and Click in column A row 4,then run the following script:
TypeText Tab
TypeText ControlKey, “a”
TypeText ControlKey, “c”
return the remoteClipBoard

Hi Dan,
The format of the Excel will define how best to approach this problem. As @Keiichi_Yamamoto mentioned, SenseTalk has native functionality for working with Excel either as a database or working cell by cell. I prefer the former, but it only applies if your column headers (Row 1) represent fields (e.g. FirstName, LastName, MI), and each row represents a record. If that is NOT the case, you will reference the sheet by cell. Which format is your data in?

Thank you all for the input, I went with the approach of using the cell by cell function. The code I ended up successfully using is shown below but if anybody can see anyway to improve it then by all means let me know.

set MyExcelFile to Workbook(ResourcePath(“responses_replies.xlsx”))
set worksheet to Worksheet(MyExcelFile, 1)
put cellRange(worksheet, “A1:A4”) into question

put readText([“ReadRoom_A”, “ReadRoom_B”] into B
put the last word of B into C
log C

if the first item of question contains C then
typetext cell(worksheet, “B1”)
end if
if the second item of question contains C then
typetext cell(worksheet, “B2”)
end if
if the third item of question contains C then
typetext cell(worksheet, “B3”)
end if
if the fourth item of question contains C then
typetext cell(worksheet, “B4”)
end if

…and so on

I think you could replace all of your if statements with something along these lines:

put cellRange(worksheet, "B") into B

put the index number of the item containing C in question into questionNumber
put item questionNumber of B into reply
typeText reply

Thanks Doug, the script you provided does (as expected) suit the question so thank you very much for helping. However when I run it, the results returned are correct but not presented in the right structure. The returned result in the log is:

[@" I was able to find the word"Chat""]

When what I would like it to be returned as is:

“I was able to find the word Chat”

My original script does return the correct format but as you can see it includes an ever growing list of IF statements which may eventually become too large to manage.

Any thoughts would be greatly appreciated!

Ah. I had forgotten that cellRange() always returns a list of lists, so the value of item questionNumber of B in my suggestion is actually a list containing the cell value rather than the value itself. Change that line to this and it should get the actual value:

put item 1 of item questionNumber of B into reply

thank you Doug, the slight change fixed everything!