CODEWITHSUNDEEP
google-apps-script
Ben C
Ben C

Reputation: 61

Update values in a Google sheet with values from another sheet

I'm trying to set up some sheets to run invoicing and stock control for my business. I have an invoice template, the sheet is called "Shop" where col A rows 22 to 41 have stock codes and col B rows 22 to 41 have the quantities to be sold.

The script I need will find the value in Shop A22 in another sheet (called "Stock" between A5 & I1000) and update the quantity in stock which is held in col 5 and the quantity sold held in col 7.

At present I have this working in Excel using the two modules shown below but I don't know how to get them to work in Sheets.

Could anyone help please?

Thanks

Sub UpdateStockDetailsDjB()
    Dim intRow As Integer
    Dim intStk As Integer
    Dim strPrd As String
' Serially process cells A22 thru A41
    intRow = 22
    Do
        If Worksheets("Shop").Cells(intRow, 1).Value = "" Then
            Exit Do
        End If
        Rem strPrd = Left(Trim(Worksheets("Shop").Cells(intRow, 1).Value), 5)
        strPrd = (Worksheets("Shop").Cells(intRow, 1).Value)
            intStk = fncFindPrdStk(strPrd) ' Stock Code row in ShopStock sheet
        Worksheets("Stock").Cells(intStk, 5).Value = Worksheets("Stock").Cells(intStk, 5).Value - Worksheets("Shop").Cells(intRow, 2).Value ' Update Qty in Stock
        Worksheets("Stock").Cells(intStk, 7).Value = Worksheets("Stock").Cells(intStk, 7).Value + Worksheets("Shop").Cells(intRow, 2).Value ' Update Used / Sold
        If intRow = 41 Then
            Exit Do
        End If
        intRow = intRow + 1
    Loop
' End
Call Clearhop
End Sub


Function fncFindPrdStk(strPrd As String) As Variant
    Dim intRow As Integer
' Find Stock Code row in Stock sheet cells A3 thru A1010
    intRow = 3
    Do
        If Trim(Worksheets("Stock").Cells(intRow, 1).Value) = strPrd Then
            Exit Do
        End If
        If intRow = 1510 Then
            MsgBox "Stock Code MISSING: " & strPrd, vbCritical
            Exit Do
        End If
        intRow = intRow + 1
    Loop
' Return Stock Code row
    fncFindPrdStk = intRow
End Function

Upvotes: 1

Views: 698

Answers (1)

TheWizEd
TheWizEd

Reputation: 8616

Description

I'm not able to test this since it relies on several sheets and specific data in particular row, and rather than try to construct a data set for testing I just have to hope I've translated everything correctly.

Any way this at least shows the basics of how I would do it. Its a start.

What is unclear is how the function is executed.

I have tried to create test data to test this script and have made several revisions. Please replace the script with the latest.

Code.gs

function updateStockDetailsDjB() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let shop = spread.getSheetByName("Shop");
    let stock = spread.getSheetByName("Stock");
    let stockValues = stock.getRange(5,1,1006,7).getValues(); // A5:G1010
    let shopValues = shop.getRange(22,1,20,2).getValues(); // A22:B41
    for( let i=0; i<shopValues.length; i++ ) {
      if( shopValues[i][0] === "" ) break;
      let strPrd = shopValues[i][0];
      let index = fncFindPrdStk(strPrd,stockValues);
      // Remember array index is 1 less then column number
      stockValues[index][4] = stockValues[index][4]-shopValues[i][1]; // Update Qty in Stock
      stockValues[index][6] = stockValues[index][6]+shopValues[i][1]; // Update Used / Sold
    }
    // In case there are any formulas in the columns lets extract columns E(4) an G(6)
    let column = stockValues.map( row => [row[4]] );
    stock.getRange(5,5,stockValues.length,1).setValues(column);
    column = stockValues.map( row => [row[6]] );
    stock.getRange(5,7,stockValues.length,1).setValues(column);
  }
  catch(err) {
    console.log(err);
    // If run from Spreadsheet context you could use this
    // SpreadsheetApp.getUi().alert(err);
  }
}

function fncFindPrdStk(strPrd,values) {
  // values is only up to row 1010
  let index = values.findIndex( row => row[0] === strPrd );
  if( index < 0 ) throw  "Stock Code MISSING: " & strPrd;
  return index;
}

References

Upvotes: 2

Related Questions