javascript - Script to add 2 cells together on multiple rows of a Googlesheet - Stack Overflow

admin管理员组

文章数量:1344934

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

Share Improve this question Follow asked yesterday Lester DUTALester DUTA 1311 silver badge44 bronze badges New contributor Lester DUTA is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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

本文标签： javascriptScript to add 2 cells together on multiple rows of a GooglesheetStack Overflow