I am using this function which works upon submitting the entry via Google Forms. Currently the below script is adding increment number to the Sheet1 Last empty row

I want to include two more sheets where same incremental number will be added to last empty row.

Column will be same where increment number is pasting that is Column A but Sheet1 data starts from Row2 and Sheet2 and Sheet3 Data starts from row6.

Your help will be much appreciated.

  function uPDATEiT() {

  var aiColumnName = 'A'; //Sheet1,Sheet2,Sheet3 same column
  var requieredColName = 'C' //it is just for Sheet1

      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var worksheet = ss.getSheetByName('Sheet1','Sheet2','Sheet3')

  var aiColRange = worksheet.getRange(aiColumnName   '1:'   aiColumnName   '1000');
  var aiCol = aiColRange.getValues();
  var aiColIndex = aiColRange.getColumn();
  var reqCol = worksheet.getRange(requieredColName   '1:'   requieredColName   '1000').getValues();

  var maxSeq = 0;
  for (var i = 0; i <= aiCol.length; i  ) {
    if (parseInt(aiCol[i], 10) > maxSeq) { maxSeq = aiCol[i]; }
  }
  for (var i = 0; i <= aiCol.length; i  ) {
    if ((''   reqCol[i]).length > 0 && (''   aiCol[i]).length === 0) {
      maxSeq  ;
      worksheet.getRange(i   1, aiColIndex).setValue(maxSeq);


    }
  }
}

CodePudding user response：

You can use this formula in Sheet2!A4 and Sheet3!A4:

={"ID's";ARRAYFORMULA(IF(B5:B<>"",vlookup(B5:B,{'Data Sheet (Sheet1)'!$B$2:$B,'Data Sheet (Sheet1)'!$A$2:$A},2,false),""))}

What it does?

• Check if column B is not empty, then get the id from Sheet1 based on the matched name(column B) as a key in the

  Sheet 2 using the formula:

  

  Update

  If you just want to append your maxSeq in Sheet2, you can use this:

  Code:

  function uPDATEiT() {
  
    var aiColumnName = 'A'; //Sheet1,Sheet2,Sheet3 same column
    var requieredColName = 'C' //it is just for Sheet1
  
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var worksheet = ss.getSheetByName('Data Sheet (Sheet1)')
    var sheet2 = ss.getSheetByName('Sheet2')
    Logger.log(worksheet)
    Logger.log(sheet2.getLastRow())
    var aiColRange = worksheet.getRange(aiColumnName   '1:'   aiColumnName   '1000');
    var aiCol = aiColRange.getValues();
    var aiColIndex = aiColRange.getColumn();
    var reqCol = worksheet.getRange(requieredColName   '1:'   requieredColName   '1000').getValues();
  
    var maxSeq = 0;
    for (var i = 0; i <= aiCol.length; i  ) {
      if (parseInt(aiCol[i], 10) > maxSeq) { maxSeq = aiCol[i]; }
    }
    for (var i = 0; i <= aiCol.length; i  ) {
      if ((''   reqCol[i]).length > 0 && (''   aiCol[i]).length === 0) {
        maxSeq  ;
        worksheet.getRange(i   1, aiColIndex).setValue(maxSeq);
        sheet2.getRange(sheet2.getLastRow() 1,aiColIndex).setValue(maxSeq);
  
      }
    }
  }
  

  • Get the last row in the sheet that contains data using

    CodePudding user response：

    Probably it can be something simple like this:

    ss.getSheetByName('Sheet2').appendRow([maxSeq]);
    

    It will add the maxSeq value into column A after the last non-empty row on the Sheet2.