-1

in a jxa script i am running a shell script that runs osascript with mulitple -e arguments containing a multiline applescript which normally work just fine. this one will not work however. the apostrophy in the word range's is not being interpreted propertly with the doShellScript command. the same command on the cli works just fine.

in particular -e 'set theData to used range's value' argument.

i tried range\'s, range\\'s, range\\\'s, range\\\\'s, range's and many other ideas from this site but to no avail so far. following is the full line of code in question. ideas are welcome, thank you.

input = app.doShellScript("/usr/bin/osascript -e 'set startRow to 1' -e 'set StartCol to 3' -e 'set myCol to 1' -e 'set myRow to 1' -e 'set pollListImportIsYes to {}' -e 'set pollList to {}' -e 'tell application \"Microsoft Excel\"' -e 'tell active sheet' -e 'set theData to used range's value' -e 'repeat with colNum from StartCol to 102' -e 'set cellVal to item {colNum} of item {startRow} of theData' -e 'if cellVal = "y" then' -e 'repeat with rowNum from 1 to 14' -e 'set cellVal to item {colNum} of item {rowNum} of theData' -e 'copy cellVal to the end of pollList' -e 'end repeat' -e 'copy colNum to the end of pollListImportIsYes' -e 'copy cellVal to item (myCol) of item {myRow}' -e 'end if' -e 'end repeat' -e 'end tell' -e 'end tell' -e 'return pollList'");

Neal Vadekar
  • 127
  • 1
  • 3
  • Why on earth are you running applescript inside osascript inside a jxa script ? Why not pick one, and just do it all in that ? This is totally absurd. – CJK Nov 11 '22 at 01:38

1 Answers1

1

This is the JXA implementation of your osascript's AppleScript. It negates the need to worry about apostrophes, and reduces the layers of subprocesses that were being instantiated for each invocation of the three different scripting environments.

const Excel = Application('Microsoft Excel');
const Sheet = Excel.activeSheet();
const data  = Sheet.usedRange.value();

const startRow = 0,
      startCol = 2,
      myCol    = 0, 
      myRow    = 0;
 
var pollListImportIsYes = [], 
    pollList            = [];

for(let colNum = startCol; colNum < 102; colNum++){ 
    let cellVal = data[startRow][colNum];
    if (cellVal==='y') {
        for(let rowNum = 0; rowNum < 14; rowNum++){
            cellVal = data[rowNum][colNum];
            pollList.push(cellVal);
        } // for: rowNum 
        pollListImportIsYes.push(colNum); 
        Sheet.rows[myRow].columns[myCol].value = cellVal; 
    } // if 
} // for: colNum 


const input = pollList;
console.log(input);
CJK
  • 5,732
  • 1
  • 8
  • 26