Author Topic: Setting up a loop to scan a spreadsheet  (Read 3394 times)

Malic

  • Full Member
  • ***
  • Posts: 105
Setting up a loop to scan a spreadsheet
« on: May 06, 2017, 08:16:32 AM »
I am setting up a VA command for Elite with a plug that can read a google document spreadsheet.

Right now I have a two part prefix/suffix command so I can say something like:
"Where can I find" "Antimony"

Right now the suffix command has two separate lines for each item.

Quote
Else If Text Compare : [{SUFFIX}] Equals 'Antimony'
Set Text [Row] to '5'

What I realized yesterday is I can set the row to be a variable instead so I created this read it out when you give it the correct {Row} varable

Quote
Else If Text Compare : [{SUFFIX}] Equals '{TXT:SheetRange{TXT:Row}Col0}'

My question for this now is, the way it stands, I have 2 lines per item in the code, but each item is also in the 0 column of the spreadsheet. 

Is there perhaps a way for me to have a loop where it checks the {TXT:SheetRangeRow{TXT:Row}Col0} line on the spreadsheet, if it does not match add 1 to Row and loop again.

If the text matches what the suffix command was, exit loop and continue with the script.

It would exit the loop if it matches what was said, or it hits the end.  I plan on putting something at the end of the spreadsheet like "NO MORE DATA" so if it sees that it will stop and not just continue down an empty spreadsheet looking for items.

I can visualize it in my head on what it needs to do, but I am one of those people that once I see an example it clicks.. as of yet I have not been able to get this to click yet and am struggling with it.

Pfeil

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4782
  • RTFM
Re: Setting up a loop to scan a spreadsheet
« Reply #1 on: May 06, 2017, 03:50:47 PM »
If I understand correctly, you'll want something like this:
Code: [Select]
Set small int (condition) [Row] value to 1
Set Boolean [~loop] to True
Start Loop While : [~loop] Equals True
    Begin Text Compare : [{TXT:SheetRange{SMALL:Row}Col0}] Equals '{SUFFIX}'
        Write '[Green] Value found at row {SMALL:Row}' to log
        Set Boolean [~loop] to False
    Else If Text Compare : [{TXT:SheetRange{SMALL:Row}Col0}] Equals ''
        Write '[Red] Row is empty, exiting' to log
        Set Boolean [~loop] to False
    Else
        Set small int (condition) [Row] value as incremented by 1
    End Condition
End Loop
As you've not disclosed which plugin you're using exactly, I'm having to assume the row index is a number, rather than text.

I'm using a "Small Integer" for the row number; If your spreadsheet contains more than 32767 rows, you can use "Integer" instead, but you should probably consider using a real database instead.

The loop will stop when the value is ''(blank), though this can easily be swapped out for your "NO MORE DATA".

Malic

  • Full Member
  • ***
  • Posts: 105
Re: Setting up a loop to scan a spreadsheet
« Reply #2 on: May 07, 2017, 01:24:24 AM »
I do not know how to program so I am trying to understand what i am doing with this on the fly.

I think I am starting to get it, however, I made this just to make sure I understand how to make a loop, however I cannot even get it to work.  I am expecting to have it start at zero, and count up 1 each loop, and output to the VA window as it does it, and then exit the loop when it hits 10 and then say the number it got to again, but it just replies with 0 and then 0 again:


Quote
Set small int (condition) [number] value to 0
Write ' {SMALL:number}' to log
Set Boolean [~loop] to True
Start Loop While : [~loop] Equals 'True'
    Begin Small Integer Compare : [number] Equals 0
        Write ' done' to log
        Set Boolean [~loop] to False
    Else If Small Integer Compare : [number] Does Not Equal 10
        Set small int (condition) [number] value as incremented by 1
        Write ' {SMALL:number}' to log
        Set Boolean [~loop] to True
    End Condition
End Loop
Write ' {SMALL:number}' to log


It does not do anything, I expected it to start at 0, do the loop and count in the voice attack window as it does each loop, but it does nothing.  I am unsure if the loops simply are not working or I am completely missing something.

VAP included:


Pfeil

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4782
  • RTFM
Re: Setting up a loop to scan a spreadsheet
« Reply #3 on: May 07, 2017, 01:39:49 PM »
Your .vap differs from what you've posted, but you're mixing datatypes in both.

As a boolean is used to start and run the loop, the loop action has to check that boolean, not a text value that happens to have the same name.


I still don't know which plugin you're using, so all I can do is attach my example so you can see the way the dataypes are set up.
It's not an independent loop, it relies on you calling the plugin to actually check the spreadsheet data, so unless you tell me which plugin it is and what syntax it expects, you'll have to add the action to do that yourself.

Exergist

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 405
  • Ride the lightning
Re: Setting up a loop to scan a spreadsheet
« Reply #4 on: May 23, 2017, 08:07:48 AM »
Malic what plugin are you using to read the google doc spreadsheet?