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.