google sheets - I'm trying to write a Apps Script that will add the values of one column to another column - Stack Overf

admin管理员组

文章数量:1292146

I'm trying to add the values from a range of Column D to the values of Column E, and then clear the values from Column D.

I have two issues I haven't been able to figure out.

Issue 1 - I only want it to add the values IF there is a value in the cell of the Column D range.

Issue 2 - If the result cell in Column E is blank I get "#NUM!" in my Column E Cells.

This is what I have so far:

function RankSwap(){
   const spreadsheet = SpreadsheetApp.getActive();
   const SourceRange = spreadsheet.getRange("d4:d39");
   const SourceValues = SourceRange.getValues();

   const destinationSheet = SpreadsheetApp.getActive();
   const destinationRange = destinationSheet.getRange("e4:e39");
   const destinationValues = destinationRange.getValues();

   var sheet = SpreadsheetApp.getActive();
   var prevrank = sheet.getRange("e4:e39");
   var devrank = sheet.getRange("d4:d39"); // Define the range to clear

  if (devrank.getValue().length > 0) return;
    for (let i = 0; i < SourceValues.length; i++)
      destinationValues[i][0] = parseFloat(destinationValues[i][0]) + parseFloat(SourceValues[i][0])

    destinationRange.setValues(destinationValues);

   devrank.clearContent()
}

I'm trying to add the values from a range of Column D to the values of Column E, and then clear the values from Column D.

I have two issues I haven't been able to figure out.

Issue 1 - I only want it to add the values IF there is a value in the cell of the Column D range.

Issue 2 - If the result cell in Column E is blank I get "#NUM!" in my Column E Cells.

This is what I have so far:

function RankSwap(){
   const spreadsheet = SpreadsheetApp.getActive();
   const SourceRange = spreadsheet.getRange("d4:d39");
   const SourceValues = SourceRange.getValues();

   const destinationSheet = SpreadsheetApp.getActive();
   const destinationRange = destinationSheet.getRange("e4:e39");
   const destinationValues = destinationRange.getValues();

   var sheet = SpreadsheetApp.getActive();
   var prevrank = sheet.getRange("e4:e39");
   var devrank = sheet.getRange("d4:d39"); // Define the range to clear

  if (devrank.getValue().length > 0) return;
    for (let i = 0; i < SourceValues.length; i++)
      destinationValues[i][0] = parseFloat(destinationValues[i][0]) + parseFloat(SourceValues[i][0])

    destinationRange.setValues(destinationValues);

   devrank.clearContent()
}

• google-sheets
• google-apps-script

Share Follow edited Feb 15 at 1:55 Wicket 38.4k99 gold badges7878 silver badges192192 bronze badges asked Feb 13 at 9:17 D BD B 1111 silver badge11 bronze badge 1

• How about if(!isNaN(destinationValues[i][0]) ) for Issue 1 – Cooper Commented Feb 13 at 14:41

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

In your first issue, I added this: The if (sourceVal !== 0) condition ensures that only non-empty values in Column D are added to Column E.

Issue 1 - I only want it to add the values IF there is a value in the cell of the Column D range.

For your second issue, I made sure to prevent #NUM! errors when Column E is blank. The || 0 ensures that empty cells are treated as 0 instead of NaN.

Issue 2 - If the result cell in Column E is blank I get "#NUM!" in my Column E Cells.

You can try this modified code:

function RankSwap() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
 const sourceRange = sheet.getRange("D4:D39");
 const destRange = sheet.getRange("E4:E39");


 const sourceValues = sourceRange.getValues();
 const destValues = destRange.getValues();


 const updatedValues = destValues.map((row, i) => {
   let sourceVal = parseFloat(sourceValues[i][0]) || 0;
   let destVal = parseFloat(row[0]) || 0;


   return [sourceVal !== 0 ? destVal + sourceVal : row[0]];
 });


 destRange.setValues(updatedValues);
 sourceRange.clearContent();
}

This script simply adds values from Column D to Column E only if Column D has a value, then clears Column D.

I use the map() function to efficiently process each row in a single loop. Instead of using a for loop with manual index handling, map() iterates through the data, applies transformations, and returns a new array in a cleaner and more readable way.

Sample Data : Before Running the code

Source	Destination
1	5
2	5
4	5
5
6	5
7	5

Sample Data : After Running the code

Source	Destination
	6
	7
	9
	5
	11
	12

本文标签：