Automatically increment value by search /compare and match two columns in Google Sheets Script

Question

I currently have to manually enter Sheet1 column B, but I'd like to auto increment it every time the script runs.

I am using a Google Sheets script.

This part is working.

How it works: Sheet2 column A is a drop down list imported from Sheet1 column A

if Sheet2 column B is anniversary of todays date Sheet 2 column F is automated to be 'reset'

the working script then deletes the rows in Sheet 2 where F is 'reset' and adds new rows for each to the bottom

Currently using this script

function writeupReset() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  var s = ss.getSheetByName('Sheet2');
  var r = s.getRange('C:C');
  var v = r.getValues();

  for(var i=v.length-1;i>=0;i--){
    if(v[0,i]=='reset'){
      s.insertRowsAfter(251,1);
      s.deleteRow(i+1);
    }
  }
}

Example of the working bits using todays date - 6/2/20

      Sheet1                Sheet2  (before script)           Sheet2  (after script)

      A      B              A       B         C               A       B         C
  |---------------|     |-------------------------|       |-------------------------|
1 | name | resets |   1 | name  |  date   | delet |     1 | name  |  date   | delet |
  |---------------|     |-------------------------|       |-------------------------|
2 | bill |   1    |   2 | bill  | 6/2/19  | reset |     2 | alex  |  9/5/19 |       |
  |---------------|     |-------------------------|       |-------------------------|
3 | mark |   5    |   3 | alex  |  9/5/19 |       |     3 | adam  | 11/6/19 |       |
  |---------------|     |-------------------------|       |-------------------------|
4 | holy |   2    |   4 | adam  | 11/6/19 |       |     4 | tony  | 12/1/19 |       |
  |---------------|     |-------------------------|       |-------------------------|
5 | tony |   0    |   5 | mark  | 6/2/19  | reset |     5 |       |         |       |
  |---------------|     |-------------------------|       |-------------------------|
6 | alex |   2    |   6 | tony  | 12/1/19 |       |     6 |       |         |       |
  |---------------|     |-------------------------|       |-------------------------|
7 | june |   1    |   7 |       |         |       |     7 |       |         |       |
  |---------------|     |-------------------------|       |-------------------------|
8 | jack |   0    |   to 252 rows (last two hidden)     to 252 rows (last two hidden)
  |---------------|   
9 | adam |   2    |   
  |---------------|   

So how can I edit the script to automatically increment Sheet1 column B when the script deletes those 'reset' rows in Sheet1?

Answer 1

I believe your goal as follows.

• You want to increase the values of the column "B", that the name is the same , in "Sheet1" when the rows with reset in "Sheet2" are deleted by the script.
  • In your case, the same names are included in the column "A" in "Sheet2".
• You want to achieve this by modifying your script in your question.

For this, how about this answer?

Modification points:

• In this case, it is required to know the values of "Sheet1" and compare them with the values of "Sheet2". The flow is as follows.
  1. Delete rows in "Sheet2".
     • At that time, an object including the information of the deleted rows is created.
  2. Increase values of the column "B" in "Sheet1".
     • The values of the column "B" are updated using the object, and the updated values are put to the sheet.

When above points are reflected to your script, it becomes as follows.

Modified script:

function writeupReset() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // 1. Delete rows in Sheet2.
  var s = ss.getSheetByName('Sheet2');
  var r = s.getRange('A1:C' + s.getLastRow());  // Modified
  var v = r.getValues();
  var obj = {};  // Added
  for(var i=v.length-1;i>=0;i--){
    if(v[i][2] == 'reset') {  // Modified
      s.insertRowsAfter(251,1);
      s.deleteRow(i+1);
      var name = v[i][0]  // Added
      obj[name] = obj[name] ? obj[name] + 1 : 1;  // Added
    }
  }

  // I added below script.
  // 2. Increase values of the column "B" in Sheet1.
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange("A2:B" + sheet.getLastRow());
  var values = range.getValues().map(([a, b]) => ([obj[a] ? b += obj[a] : b]));
  range.offset(0, 1, values.length, 1).setValues(values);
}

Note:

• In this modified script, it supposes that each value of the column "A" in "Sheet1" is the unique value in all values. From your question, I could understand like this.
• If the sheet names of "Sheet1" and "Sheet2" are different from your actual situation, please modify them.
• If your actual situation is different from the structure of your sample values in your question, this modified script might not work. So please be careful this.

References:

• map()
• setValues(values)