IndigoServer to Excel - AppleScript to Python

Posted on
Mon Nov 26, 2018 6:14 am
Lalit offline
Posts: 71
Joined: Jul 05, 2006
Location: Paris (France)

IndigoServer to Excel - AppleScript to Python

Hi,

I had to learn the Python syntax as AppleScript will be deprecated in the next version.
I spent hours to rewrite my triggers, schedules and action groups using Python and it's almost done.

I have one remaining AppleScript: its purpose is to copy 3 variables in an Excel file.

maison
entree
couloir

Here is the code I started to convert:
Code: Select all

import applescript

maison = indigo.variables[1054921485] # "CompteurMaison"
entree = indigo.variables[202160838] # "CompteurEntree"
couloir = indigo.variables[1615311783] # "CompteurCouloir"


Script ='''

property hier : string

tell application "IndigoServer"
   set maison to value of variable "CompteurMaison"
   set entree to value of variable "CompteurEntree"
   set couloir to value of variable "CompteurCouloir"
end tell

my dateActuelle()

tell application "System Events"
   tell application "Finder" to open (name of startup disk & ":Library:Application Support:Perceptive Automation:Indigo 7.2:Databases:Compteurs de passages.xlsx") as alias
end tell

delay 3

tell application "Microsoft Excel"
   set derniereLigne to first row index of (find column 2 what "")
   
   set derniereLigneDate to ("B" & derniereLigne)
   set value of cell derniereLigneDate to hier
   
   if maison > 0 then
      set derniereLigneCompteurMaison to ("C" & derniereLigne)
      set value of cell derniereLigneCompteurMaison to maison
   end if
   
   if entree > 0 then
      set derniereLigneCompteurEntree to ("D" & derniereLigne)
      set value of cell derniereLigneCompteurEntree to entree
   end if
   
   if couloir > 0 then
      set derniereLigneCompteurCouloir to ("E" & derniereLigne)
      set value of cell derniereLigneCompteurCouloir to couloir
   end if
   
   «event coreslct» cell derniereLigneDate
   
   delay 3
   
   save

end tell


on dateActuelle()
   set hier to (current date) - 86400
   set {jourChiffre, mois, annee} to {day, month, year} of hier
   set listeMoisUS to {January, February, March, April, May, June, July, August, September, October, November, December}
   
   repeat with i from 1 to 12
      if month of hier is (item i of listeMoisUS) then set moisChiffre to i
   end repeat
   
   set hier to (jourChiffre & "/" & moisChiffre & "/" & annee) as string
end dateActuelle
'''


ScriptComplet = applescript.AppleScript(source= Script)
reply = ScriptComplet.run()

How can I rid of
Code: Select all
tell application "IndigoServer"
   set maison to value of variable "CompteurMaison"
   set entree to value of variable "CompteurEntree"
   set couloir to value of variable "CompteurCouloir"
end tell
in my script and use the Python variables (maison, entree and couloir) instead?

Thanks for your help.

Posted on
Mon Nov 26, 2018 10:55 am
jay (support) offline
Site Admin
User avatar
Posts: 18200
Joined: Mar 19, 2008
Location: Austin, Texas

Re: IndigoServer to Excel - AppleScript to Python

Just substitute the values directly into your AppleScript source (untested but should be close) using the format() function:

Code: Select all
import applescript

maison = indigo.variables[1054921485] # "CompteurMaison"
entree = indigo.variables[202160838] # "CompteurEntree"
couloir = indigo.variables[1615311783] # "CompteurCouloir"

Script ='''

property hier : string

set maison to "{}"
set entree to "{}"
set couloir to "{}"

my dateActuelle()

tell application "System Events"
   tell application "Finder" to open (name of startup disk & ":Library:Application Support:Perceptive Automation:Indigo 7.2:Databases:Compteurs de passages.xlsx") as alias
end tell

delay 3

tell application "Microsoft Excel"
   set derniereLigne to first row index of (find column 2 what "")
   
   set derniereLigneDate to ("B" & derniereLigne)
   set value of cell derniereLigneDate to hier
   
   if maison > 0 then
      set derniereLigneCompteurMaison to ("C" & derniereLigne)
      set value of cell derniereLigneCompteurMaison to maison
   end if
   
   if entree > 0 then
      set derniereLigneCompteurEntree to ("D" & derniereLigne)
      set value of cell derniereLigneCompteurEntree to entree
   end if
   
   if couloir > 0 then
      set derniereLigneCompteurCouloir to ("E" & derniereLigne)
      set value of cell derniereLigneCompteurCouloir to couloir
   end if
   
   «event coreslct» cell derniereLigneDate
   
   delay 3
   
   save

end tell


on dateActuelle()
   set hier to (current date) - 86400
   set {jourChiffre, mois, annee} to {day, month, year} of hier
   set listeMoisUS to {January, February, March, April, May, June, July, August, September, October, November, December}
   
   repeat with i from 1 to 12
      if month of hier is (item i of listeMoisUS) then set moisChiffre to i
   end repeat
   
   set hier to (jourChiffre & "/" & moisChiffre & "/" & annee) as string
end dateActuelle
'''.format(maison.value, entree.value, couloir.value)

ScriptComplet = applescript.AppleScript(source= Script)
reply = ScriptComplet.run()

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Tue Nov 27, 2018 11:17 am
Lalit offline
Posts: 71
Joined: Jul 05, 2006
Location: Paris (France)

Re: IndigoServer to Excel - AppleScript to Python

Thank you Jay!

I've learned one more thing with .format().

I had a lot of problem to insert the date in Excel as string, because I had strange results.
After hours of struggling (yeah I know it's a shame...), I succeeded!

I had to delete the AppleScript handler on dateActuelle().
Then I split the date in Python into 3 variables.
Code: Select all
Veille = date.today() - timedelta(1)

Jour = Veille.strftime('%d')
Mois = Veille.strftime('%m')
Annee = Veille.strftime('%Y')
Those variables were concatenated in the AppleScript
Code: Select all
set hier to ({} & "/" & {} & "/" & {}) as string
Unfortunately, it wasn't possible to use this variable as string below in Applescript because in Excel it was wrong, That's why I lost so many time!
Code: Select all
hier = Veille.strftime('%d/%m/%Y')

So here is the full code
Code: Select all
from datetime import date, timedelta
import applescript

Maison = indigo.variables[1054921485] # "CompteurMaison"
Entree = indigo.variables[202160838] # "CompteurEntree"
Couloir = indigo.variables[1615311783] # "CompteurCouloir"

Veille = date.today() - timedelta(1)

Jour = Veille.strftime('%d')
Mois = Veille.strftime('%m')
Annee = Veille.strftime('%Y')


Script ='''

set maison to "{}"
set entree to "{}"
set couloir to "{}"

set hier to ({} & "/" & {} & "/" & {}) as string

tell application "System Events"
   tell application "Finder" to open (name of startup disk & ":Library:Application Support:Perceptive Automation:Indigo 7.2:Databases:Compteurs de passages.xlsx") as alias
end tell

delay 3

tell application "Microsoft Excel"
   set derniereLigne to first row index of (find column 2 what "")
   
   set derniereLigneDate to ("B" & derniereLigne)
   set value of cell derniereLigneDate to hier
   
   if maison > 0 then
      set derniereLigneCompteurMaison to ("C" & derniereLigne)
      set value of cell derniereLigneCompteurMaison to maison
   end if
   
   if entree > 0 then
      set derniereLigneCompteurEntree to ("D" & derniereLigne)
      set value of cell derniereLigneCompteurEntree to entree
   end if
   
   if couloir > 0 then
      set derniereLigneCompteurCouloir to ("E" & derniereLigne)
      set value of cell derniereLigneCompteurCouloir to couloir
   end if
 
   delay 3 
   save
end tell

'''.format(Maison.value, Entree.value, Couloir.value, Jour, Mois, Annee)

ScriptComplet = applescript.AppleScript(source= Script)
reply = ScriptComplet.run()
Thanks a lot!

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest