0

I am trying to loop over a column in the spread sheet. For each cell in the column I am looping over, I want to multiply the value of the cell by the value in the cell previous in its row (the cell would be the previous cell in the row, i.e I would loop over column 2 and for A2 I would want to multiply the value in that cell by A1, and then this would be stored in cell A3 and the the loop would continue to B2. In B2 I want to multiply the cell value by B1 and store the result in B3 and so on). I then want to store this total value in next cell in the row.

Here is an example of the data I am trying to automate

https://i.stack.imgur.com/ggjIB.png

I can't seem to finda clear way to do this, is this even possible with pygsheets? Thanky ou for your help, I am at a loss here!

I have figured out how to loop over a single column with just a basic for loop where it returned the individual calues of the column back in a list but I am not seeing how this list generated from the loop could be modified to do the above.

Edit: Here is my code so far. I got it to almost work but the the "cellcount" is not going up with each for loop iteration.

```python
    first_column = wks.get_col(3)
first_column_data = first_column[3:]

cellcount = 4
testnumber = "17"
columntotal = "D" + str(cellcount)
columnquantity = "B" + str(cellcount)

wks.update_value(columntotal, testnumber)

   

 for values in first_column_data:

     variable = wks.get_value(columnquantity)
     quantity = 0
     if values == "BARBLON-12":
        quantity += 2
     else:
        pass
   stockvalue = quantity * int(variable)
   wks.update_value(str(columntotal), str(stockvalue))
   quantity = 0
   cellcount += 1

    ```
Neslinx
  • 1
  • 2
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand the relationship between your question and your provided image. Can I ask you the detail of your question? For example, can you provide the sample input and output situations you expect? By the way, from your tag of `pygsheets`, have you already finished the preparation for using `pygsheets`? – Tanaike Jan 16 '23 at 02:26
  • No worries, thank you for looking at it. I am set up to use pygsheets and am connected to my Google sheet. Sorry for not being very clear, I hope this can help. I am wanting to iterate with a "for loop" over the data in column "D". For each value in that loop, I want to multiply the number by the value in the cell to the left of the current cell in the iteration. For example I am trying to make it so that the value of D4 is multiplied by the value of C4 and then inputted to E4, then it moves on to the next item in the loop, D5 which would be multiplied by C5 and inputted to E5. – Neslinx Jan 16 '23 at 03:54
  • I don't have the input other than just "for value in row: result = value * [previous cell?? ]" I am stuck on how to reference the previous cell with pygsheets instead of directly entering in each cell individually. Ideally I want to do this across hundreds of numbers. – Neslinx Jan 16 '23 at 03:57
  • Thank you for replying. I have to apologize for my poor English skill again. I think that the logic of `For example I am trying to make it so that the value of D4 is multiplied by the value of C4 and then inputted to E4, then it moves on to the next item in the loop, D5 which would be multiplied by C5 and inputted to E5.` in your reply might be different from – Tanaike Jan 16 '23 at 05:04
  • `i.e I would loop over column 2 and for A2 I would want to multiply the value in that cell by A1, and then this would be stored in cell A3 and the the loop would continue to B2. In B2 I want to multiply the cell value by B1 and store the result in B3 and so on). I then want to store this total value in next cell in the row.` of your question. So, I cannot still understand your question. I apologize for this. But, I would like to support you. When I could correctly understand your question, I would like to think of a solution. – Tanaike Jan 16 '23 at 05:04
  • Hey sorry for the delays, I have been caught up in work. I have updated the question with my code. I almost got it do what I want it to do but now the cellcount won't increase from the for loop. – Neslinx Jan 27 '23 at 17:57
  • Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand the logic for achieving your expected result. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. – Tanaike Jan 28 '23 at 00:35

0 Answers0