translation project only uses first and last items in a Numbers cell range

I need to translate various items from a Numbers file. I used chatGPT to help me write a script which receives a cell range and translates them using Google Translate. The problem I am having is that it only translates the first and last items in the desired cell range. Please help me integrate a way to have it translate the whole range, i.e (D435:D440). Here is the script:

-- Define the document file path
set filePath to "file path here"

-- Define the sheet, table, and cell range
set sheetName to "Sheet 1"
set tableName to "Table 1"
set cellRange to "D429:D433"

-- Function to translate text from Spanish to English using Google Translate API
on translateText(textToTranslate)
	set baseURL to "https://translate.googleapis.com/translate_a/single?client=gtx&sl=es&tl=en&dt=t&q="
	set encodedText to do shell script "python -c \"import urllib, sys; print urllib.quote(sys.argv[1])\" " & quoted form of textToTranslate
	set translatedText to do shell script "curl -s \"" & baseURL & encodedText & "\""
	set translatedText to my parseTranslatedText(translatedText)
	return translatedText
end translateText

-- Function to parse the translated text
on parseTranslatedText(translatedText)
	try
		set translatedText to quoted form of translatedText
		set translatedText to do shell script "python -c \"import sys, json; print json.loads(sys.argv[1])[0][0][0]\" " & translatedText
		return translatedText
	on error errMsg
		return "Error translating text"
	end try
end parseTranslatedText

-- Function to get the contents of a cell range
on getCellRangeValues(filePath, tableName, sheetName, cellRange)
	set cellValues to {}
	tell application "Numbers"
		set doc to open filePath
		tell sheet sheetName of doc
			set tbl to table tableName
			repeat with cellRef in words of cellRange
				set end of cellValues to value of cell cellRef of tbl
			end repeat
			close doc saving no
		end tell
	end tell
	return cellValues
end getCellRangeValues

-- Function to set the translated text in a cell range
on setTranslatedValues(filePath, tableName, sheetName, cellRange, translatedValues)
	tell application "Numbers"
		set doc to open filePath
		tell sheet sheetName of doc
			set tbl to table tableName
			repeat with i from 1 to count of words in cellRange
				set cellRef to word i of cellRange
				set value of cell cellRef of tbl to item i of translatedValues
			end repeat
			close doc saving yes
		end tell
	end tell
end setTranslatedValues

-- Main translation process
try
	-- Get the Spanish texts from the specified cell range
	set spanishTexts to getCellRangeValues(filePath, tableName, sheetName, cellRange)
	
	-- Translate the Spanish texts to English
	set translatedTexts to {}
	repeat with textToTranslate in spanishTexts
		set translatedText to translateText(textToTranslate)
		set end of translatedTexts to translatedText
	end repeat
	
	-- Set the translated texts in the specified cell range
	setTranslatedValues(filePath, tableName, sheetName, cellRange, translatedTexts)
	
	display dialog "Translation completed successfully."
on error errMsg
	display dialog "Error: " & errMsg
end try

The only way i have been able to bypass cell access errors is by indicating both the sheet and table numbers. Also I found a post that metions using the word 'Item' when working with a specific cell in a cell range. Thank you for your help.

Replies

It is only translating the first and last items because in the getCellRangeValues handler, that is all that the words of cellRange list contains. You would need to get the individual cell elements of the range and use those in the repeat statement to get the in between cell references, for example:

	repeat with cellRef in cells of range cellRange of tbl
		set end of cellValues to value of cellRef
	end repeat