1

I am trying to find a way to auto increment a number to let the cell value follow. This is what I have for now.

//worksheets
const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("QR CODE GENERATOR");
//lastrow
const lastrow_ws = ws.getLastRow();

function createQRCode(){
  var startPoint = ws.getRange(2, 2);
  var startPoint_value = ws.getRange(2, 2).getValue();
  var qrRange = ws.getRange(lastrow_ws, 2);
  var i = 3;
  if (startPoint_value == ""){
  startPoint.setValue('=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&C2)');
  } else {
    qrRange.setFormula('=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&C' + i + ')');
    i++
    }
}

For this script, I would simply like it to place a QR Code Generator on a cell each time it is called. The script is called through a trigger onFormSubmit.

If B2 is empty,

Enter =IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&E2)

and if B2 is not Empty,

Enter =IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&E3) and so on to E1000 etc.

I was thinking a For Loop might be the answer but I have no idea how to implement it in this situation. I have a done a similar thing using ForEach but I can't apply it in this situation.

Screenshots of desired output:

enter image description here

enter image description here

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
Dean
  • 133
  • 8
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. In order to correctly understand your question, can you provide the sample input and output situations you expect? – Tanaike Mar 02 '23 at 03:18
  • Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, from your reply, I cannot still understand your question. 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. By the way, what is the difference between `If C2 is empty,` and `if C2 is not Empty,`? – Tanaike Mar 02 '23 at 03:31
  • Can you suggest a way I may explain the output to you? are screenshots a better way for you to understand? – Dean Mar 02 '23 at 03:50
  • @Tanaike good day sir tanaike! ive updated my post to visualize the desired output for you to understand. – Dean Mar 02 '23 at 03:54
  • it should be B2! and it connects to E2. sorry for that. i have updated the script and post. thanks! – Dean Mar 02 '23 at 04:00
  • Since you are using an `onFormSubmit` trigger, if you take advantage of the [Event Objects](https://developers.google.com/apps-script/guides/triggers/events#form-submit), you can access the `range` and use that attribute to get the submission row. For example, `e.range.getRow()`. – Tedinoz Mar 02 '23 at 04:11
  • @Tedinoz ooooh, this is new to me. can you elaborate further while I take a look at Event Objects? – Dean Mar 02 '23 at 04:23
  • Thank you for replying. Unfortunately, from your reply, I couldn't understand the difference between If `C2 is empty`, and `if C2 is not Empty,`. And, I cannot understand the last row in your situation. In your situation, is the last row the row that the formula has already been put in column "B" when the script is run last time? Or, is it the row that other columns have the values, not including the formula in column "B"? – Tanaike Mar 02 '23 at 04:37
  • The C2 was a mistake in my part im sorry. It shouldve been B2. the relationship of E2 to B2 is it is where the document URL is to be converted for the QR Code. – Dean Mar 02 '23 at 04:44
  • Thank you for replying. In your question, in the case of `If B2 is empty,`, it seems that you want `=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&E2)`. And, in the case of `if B2 is not Empty,`, it seems that you want `=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&E3) and so on to E1000 etc.`. I cannot understand this difference. And, I cannot still understand about the last row in your situation. I apologize for my poor English skill. I apologize that I am trying to correctly understand your question. – Tanaike Mar 02 '23 at 04:51
  • If B2 is empty, It will place the value in cell B2 converting the url placed in cell E2, while if cell B2 already has a value, the Formula continues to cell B3 converting the url placed in E3, B4 to E4, B5 to E5 so on. – Dean Mar 02 '23 at 04:54
  • qrRange.setFormula('=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&C' + i + ')'); i++ This is my trial for that and currently it is not working. so i am trying to find another method to achieve this – Dean Mar 02 '23 at 04:55
  • Thank you for replying. From your reply, you want to put the formula to the next row of the last row of column "B". And, when the formula is used, you want to use the value of column "E" of the same row. Is my understanding correct? – Tanaike Mar 02 '23 at 04:57
  • yup! the formula is placed on the last row of column B when the Trigger is called. Column E is where the URL is inputted on Form Submit. – Dean Mar 02 '23 at 04:58
  • i was thinking a for loop would work but i wouldnt happen to know how to use it in this current situation. so any method that may help me now and in the future would be much appreciated! – Dean Mar 02 '23 at 04:59

1 Answers1

1

Sample script 1:

In your situation, when the last row of column "E" is checked, it might be suitable. So, how about the following sample script?

function createQRCode() {
  // ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).matchFormulaText(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("QR CODE GENERATOR");
  const row = sheet.get1stNonEmptyRowFromBottom(5);
  sheet.getRange(row, 2).setFormula(`=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&E${row})`);
}

Sample script 2:

From The script is called through a trigger onFormSubmit., in this sample, the event object is used. In this case, please don't directly run this function with the script editor. Please be careful about this.

function createQRCode(e) {
  const { range } = e;
  const sheet = range.getSheet();
  const row = range.rowStart;
  sheet.getRange(row, 2).setFormula(`=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=500x500&chl="&E${row})`);
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • script 2 is what i chose to use due to the minimum code used and it works really smoothly. thanks as always sir tanaike! I am currently building a coding project involving QR codes and images and most of the queries answered are still from you. From the bottom of my heart i truly thnak you for everything. God bless! – Dean Mar 02 '23 at 05:44