I have a Googlesheet that me and some friends use to keep track of our progress. What I would like is a script that adds two cells together in a row across several rows E.G H6 + C6 updates the value in H6, H7 + C7 updates the value in H7 etc. I would also like the values in C* to be cleared once the sum is completed.

The script would hopefully be able to run when a button on the sheet is clicked.

If it helps, I have been able to get this working in VBA for Excel using the following script:

Sub Mibazza()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Delivery") ' Change "Sheet1" to your sheet name

' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each row
For i = 7 To lastRow ' Assuming you start at row 2
ws.Cells(i, "J").Value = ws.Cells(i, "d").Value + ws.Cells(i, "J").Value
Next i
Range("d7:d60").ClearContents

End Sub

A value is manually input into C7. H7 is current running total.

 C7   H7
 768  768 

Once button is clicked the results should be

 C7  H7
     1536 

I have a Googlesheet that me and some friends use to keep track of our progress. What I would like is a script that adds two cells together in a row across several rows E.G H6 + C6 updates the value in H6, H7 + C7 updates the value in H7 etc. I would also like the values in C* to be cleared once the sum is completed.

The script would hopefully be able to run when a button on the sheet is clicked.

If it helps, I have been able to get this working in VBA for Excel using the following script:

Sub Mibazza()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Delivery") ' Change "Sheet1" to your sheet name

' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each row
For i = 7 To lastRow ' Assuming you start at row 2
ws.Cells(i, "J").Value = ws.Cells(i, "d").Value + ws.Cells(i, "J").Value
Next i
Range("d7:d60").ClearContents

End Sub

A value is manually input into C7. H7 is current running total.

 C7   H7
 768  768 

Once button is clicked the results should be

 C7  H7
     1536 

• javascript
• google-sheets

• 1 "If it helps". It doesn't. Remove it. Code translation questions are likely to be closed. See tag info page for official documentation, free resources and more details. – TheMaster Commented yesterday

1 Answer 1

As I understand your question, you want to add the values in column H6 and C6, update column H6 with the new total, and then clear the value in column C6.

Since you are not very familiar with Google Apps Script, Here is a step-by-step process:

• Open your Google Sheet.

• Go to Extensions : Apps Script.

• Delete any existing code and paste the script below.

• Click Save and then run the script then assign it to a button.

You can try this Code:

function addProgress() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var rangeH = sheet.getRange("H6:H");
  var rangeC = sheet.getRange("C6:C");
  var valuesH = rangeH.getValues();
  var valuesC = rangeC.getValues();

  for (var i = 0; i < valuesH.length; i++) {
    if (valuesC[i][0] !== "" && !isNaN(valuesC[i][0])) {
      valuesH[i][0] += valuesC[i][0];
    }
  }
  rangeH.setValues(valuesH);
  rangeC.clearContent();
}

As for the button:

• Go to Insert > Drawing, then choose your desired shape or image.

• After inserting it, right-click the image to open the options menu.

• Click the three dots (⋮) and select "Assign script."

• Enter the script name (addProgress) and click OK.

Sample Output:

Reference: for loop