0

So, I'm wanting to use the following line of code:

=image(CONCATENATE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&A2&+B2&+C2&+D2&+E2&+F2&+G2))

But, this formula needs to change from A2/B2/C2 to A3/B3/C3 and so on, but automatically.

USE CASE:
I'm wanting to create unique QR codes, based on the incoming data, which is written to a Google sheet. So, a form is filled out capturing certain information, is written to row A2/B2/C2 etc, in Google sheet, and the resulting QR code is generated at the end of that row. based on the above line of code. This line of code/formula is not present on the spreadsheet, but rather, is pushed to the cell when the rest of the information is submitted.

The current problem of course, the code above does not change the QR code dynamically to represent the next row of data. It hard references the previous row ad infinitum. I need it to reference the next row, dynamically.

Is this possible?

Improve this question
1
  • Are you using Apps Script or the Sheets API to add the formula to the spreadsheet?
    – Rubén ♦
    Jul 1, 2018 at 0:19

2 Answers 2

Reset to default
0

Google Sheets does this already.

Alternatives

  1. Copy and paste the formula one cell below the position of the formula to get A3/B3/C3 and so on, two cell below to theg A4/B4/C4 and so on, and so on.
  2. Do a fill down. Point to the bottom right corner of the cell having the formula click and drag until you get the required cells to have the desired formula.
Improve this answer
4
  • He says specifically that the data is coming from a form - so I highly doubt he wants to open the spreadsheet and extend the formula every time a new entry comes in - and if the formula is already on the row, the form data wont post to that row -
    – Aurielle Perlmann
    Jul 1, 2018 at 0:06
  • From the question "This line of code/formula is not present on the spreadsheet, but rather, is pushed to the cell when the rest of the information is submitted.
    – Rubén ♦
    Jul 1, 2018 at 0:10
  • right - pushed meaning it comes from somewhere else - also not requiring him to manually extend it, ideally
    – Aurielle Perlmann
    Jul 1, 2018 at 0:10
  • The copy and paste could be done programmatically by the same code that adds the formula. I'm not sure if the new autofill method works with formulas.
    – Rubén ♦
    Jul 1, 2018 at 0:16
0

You can use indirect and reference it's current row like this: 

=image("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&join("+",indirect("A"&row()&":G"&row())))

if you do need to keep the cells separate you can wrap each one with indirect("ColumnLetter"&row()) but the above is far simpler. Also since your using the & symbols, you do not need to use concatenate, it has the same result.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.