CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user53423103981023
user53423103981023

Reputation: 85

Updating a sheet with a value if strings match on two sheets

Sheet 2 has all the items and their statuses, while Sheet 1 has only some of the items from Sheet 2. I want to be able to see every time an item mentioned on Sheet 1 is listed as having a status update, i.e. e date, on Sheet 2.

Here's what I have so far, but having trouble calling the right range to work with. Is there a simpler way to do what I want to do? 

  function statusupdate() {
    var activesht = SpreadsheetApp.getActiveSpreadsheet(); 
    var statussht = activesht.getSheetByName("Sheet 2"); //get sheet on which status update occurs 
    var statusrng1 = statussht.getRangeByName('B'); 
    var statusrng2 = statussht.getRangeByName('C');
    var mainsht = activesht.getSheetByName("Sheet 1"); //get sheet where you show a specific thing has been updated, if that thing mentioned here. 
    var mainrng = mainsht.getRangeByName('F');  

    if (statusrng1 == mainrng) {
      var date = statusrng2.getValue();
      var daterng = mainrng.getRangeByName('E');

      daterng.setValues(date);
    }
  }

Upvotes: 1

Views: 110

Answers (1)

Mogsdad
Mogsdad

Reputation: 45750

Spreadsheet formula

You can have the rows in one sheet follow those in another without using a script. For example, say we have a sheet named Items that contains one row for every item we carry, with the item number in the first column.

source data

We can use VLOOKUP() to search for the row containing info about individual items, and select specific columns from it.

For example, this formula would be used in B2, and could be copied to other cells in our sheet:

=VLOOKUP($A2,Items!$A$2:$C$7,COLUMN(),false)

screenshot

Script

There are a few issues with your script.

  • .getRangeByName('B') - This method gets a named range. Given the name, I suspect you mean to get column B, and NOT a named range. If that's the case, you could use this instead:

    var statusrng1 = statussht.getRange('B:B');

    In A1Notation, the range B:B is the entire column B.

  • You intend to copy values, so there is another step required beyond identifying ranges; you need to first read the values from a range, and then later write them to a different range. For that, you need to use methods like getValues() and setValues().

Here's an updated version of your script, adapted to the example spreadsheet described above.

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

  //get sheet on which status update occurs
  var statusSheet = ss.getSheetByName("Items");
  var statusRange = statusSheet.getDataRange();
  var statusData = statusRange.getValues();

  //get sheet where you show a specific thing has been updated, if that thing mentioned here. 
  var trackingSheet = ss.getSheetByName("Tracking");
  var trackingRange = trackingSheet.getDataRange();
  var trackingData = trackingRange.getValues();

  // Loop over all rows in the Tracking sheet to update from the Items sheet
  // Start with row=1, because row 0 contains headers
  for (var row=1; row<trackingData.length; row++) {
    var item = trackingData[row][0];
    if (item == '') continue;  // skip rows without item #

    // Look for item in Items sheet
    var statusRow = null;
    for (var sRow=1; sRow<statusData.length; sRow++) {
      if (statusData[sRow][0] == item) {
        // Found our match, grab that row
        statusRow = statusData[sRow];
        break;
      }
    }

    // If we found a matching row, copy the status
    if (statusRow) {
      // Customize this depending on how your sheets are organized
      trackingData[row][1] = statusRow[1];
      trackingData[row][2] = statusRow[2];
    }
  }

  // All values have been copied to trackingData, now write to sheet
  trackingRange.setValues(trackingData);
}

Upvotes: 1

Related Questions