google-sheetsgoogle-apps-scriptdata-entry

Update / Modify Data from Data Entry Form


I have 2 sheets which is Billing Masterlist & Data Entry Form. When Data Entry Form is used, it will transfer the data into Billing Masterlist. I'm stucked with a code where I wanted to modify the data from the Data Entry Form when amended is required to the old data. Below is the code I write to modify the data but unsuccessful to modify.

Sheet for testing purpose: https://docs.google.com/spreadsheets/d/1CyALo_tE_75w1SCPCIiiqgToyYRAnZJKMdpBpW6G5mY/edit#gid=1949705865

function updateData() {
var SPREADSHEET_NAME = "Billing Masterlist"
var SEARCH_COL_year = 0;
var SEARCH_COL_hospital = 1;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Data Entry Form");

  var hospital = formSS.getRange("B7").getValue()[1];
  var year = formSS.getRange("D7").getValue()[0];

  var values = ss.getSheetByName("Billing Masterlist").getDataRange().getValues();
  var cont = 0;
  for (var i = 0; i < values.length; i++)

  {
    var row = values[i];

    if ((row[SEARCH_COL_year] == year && row[SEARCH_COL_hospital] == hospital ))

    {
      var s = (11 + cont).toString();
      var uValues =
      [[formSS.getRange("B" + s).getValue(),
      formSS.getRange("E" + s).getValue(),
      formSS.getRange("F" + s).getValue()]];
      ss.getSheetByName("Billing Masterlist").getRange(i+1, 1, 1, 63).setValues(uValues);
      cont = cont + 1;

    }
  }
}

Solution

  • You had the number of columns set incorrectly on the last last

    function updateData() {
      var ss = SpreadsheetApp.getActive();
      var sh1 = ss.getSheetByName("Data Entry Form");
      var B7 = sh1.getRange("B7").getValue()[1];
      var D7 = sh1.getRange("D7").getValue()[0];
      var values = ss.getSheetByName("Billing Masterlist").getDataRange().getValues();
      var cont = 0;
      for (var i = 0; i < values.length; i++) {
        var row = values[i];
        if ((row[0] == D7 && row[1] == B7)) {
          var r = (11 + cont).toString();
          var uValues = [[sh1.getRange(r, 2).getValue(), sh1.getRange(r, 5).getValue(), sh1.getRange(r, 6).getValue()]];
          ss.getSheetByName("Billing Masterlist").getRange(i + 1, 1, 1, 3).setValues(uValues);
          cont = cont + 1;
        }
      }
    }