Test Sheet

So I'm trying to create sort of a database to update and store data. From what I can tell, I have to more than likely create a script to run when I need to transfer that data over, but I'm having a hard to trying to figure out where to even start.

The idea is on the 'Data Collection" tab you have the names, the section they are working on, and the units they made. Once I run a script, it would transfer the names and data to the "Section Data" tab and place them in Column A then place how many units they made in the proper section cell. It would then clear the Units field in "Data Collection" so it's ready for the next day.

Here is where it gets a bit complicated. If the name already exists in the "Section Data" tab, instead of adding a new row, add the number of units to the total that is already in the cell under that name.

So in the example above, running the script would make the "Section data" tab look like "Section Data After" as an example.

Any data entered in would either increment existing data if the name already exists. If it doesn't exist, it would create a new line in the first available slot and append it to the bottom of the existing data. As an example:

So in the first tab Bob in Section 1 doing 11 Units Gina in Section 2 doing 5 Units

In the second tab, I have existing data Bob in Section 1 did 6 Units and in Section 4 did 5 Units Jeff in Section 3 did 8 Units

If I were to hit the transfer script button on the first tab, it would move the data over to the second tab like so

Bob in Section 1 now has 17 Units and Section 4 still have 5 Units as nothing was added to it Jeff would still have only 8 Units in section 3 as there was no new data added for him Gina would be added under Jeff with 5 Units in Section 2

Hopefully, I explained this well enough. Thank you.

Can anyone point me in the right direction to start?

CodePudding user response：

Here is a script to complete what you are looking for:

/** @OnlyCurrentDoc */

function transfer() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var input = ss.getSheetByName('Data Collection');
  var data = ss.getSheetByName('Section Data');
  var enteredNames = data.getRange('A:A').getValues();
  var sections = data.getRange('A2:2').getValues();

  var start = input.getRange('A3:A7').getRowIndex();
  var end = start   input.getRange('A3:A7').getNumRows();

  for(var i = start; i < end; i  ) {
    let name = input.getRange('A' i).getValue();
    if(name != '') {
    let section = Number(input.getRange('B' i).getValue());
    let units = Number(input.getRange('C' i).getValue());

    var row = enteredNames.findIndex(row => row.includes(name)) 1;
    var col = sections[0].indexOf(section) 1;
    
    if(row > 0) {
      var oldUnits = data.getRange(row, col).getValue();
      if(oldUnits != '') {units = Number(oldUnits) units};
        data.getRange(row,col).setValue(units);
    } else {
      if(units == 0) {units = ''};
      let inputHere = data.getLastRow() 1;
      data.getRange('A' inputHere).setValue(name);
      data.getRange(inputHere, col).setValue(units);
    }
  }
  }
  input.getRange('C3:C7').clearContent();
};

This function iterates through each name in the Collection sheet, pulls the relevant and related data (section, units), and searches the Data sheet to see if that name is already in there. If the name is, it then checks to see if there is a value in the section it's looking for, and if there is it adds it. Otherwise, it just places the number. If there is no name match in the Data sheet, it adds them to the end of the list (and inputs the correct units under the section).

Please let me know if you have any issues with this.

Below is a function you can add to the same script file, or a different one. It creates a menu at the top so you can easily run the transfer function.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Functions')
                .addItem('Transfer Data', 'transfer')
                .addToUi();
}

Here is the link to the sheet where I tested this. It is a direct copy of the sheet you provided, the only difference being that the script is linked to it.

https://docs.google.com/spreadsheets/d/1vGV3sypyfMviyv9k2lQt4CGunDkzYf-4TX-bp8goKz4/edit?usp=sharing

If you would like me to further explain my code, or have any other questions, please let me know.