Instructions for subtracting the value of a cell in column 22 from a cell in column 24 within the same row when a change trigger occurs utilizing Google Apps Script

I need help modifying the script below to only subtract the row on which the change is made, instead of subtracting all rows in the sheet when the on-change trigger executes.


  var sourceSpreadsheetID = '1r4e4BNKwsmdC2Ry93Mq-N49zj3DAZVpHG21TgTe0FWY';
  var sourceWorksheetName = "CONCRETE";
  
  var docketSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var docketsheet = docketSpreadsheet.getSheetByName(sourceWorksheetName);
  var maxRows = docketsheet.getMaxRows();
  var loadRange = docketsheet.getRange(2, 22, maxRows);  
//row, column, number of rows
  var totalRange = docketsheet.getRange(2, 24, maxRows);
  
  var soldValues = loadRange.getValues();
  var totalValues = totalRange.getValues();
  for (var row in soldValues) {
    var soldCellData = soldValues[row][0];
    var totalCellData = totalValues[row][0];

    if (soldCellData !== "" && totalCellData !== "") {
      totalValues[row][0] = totalCellData - soldCellData;
    }
  }
  loadRange.setValues(soldValues);
  totalRange.setValues(totalValues);

Answer №1

function myFunction() {
  const ss = SpreadsheetApp.openById('1r4e4BNKwsmdC2Ry93Mq-N49zj3DAZVpHG21TgTe0FWY');
  const sheet = ss.getSheetByName("CONCRETE");
  const dataRange = sheet.getRange(2, 22, sheet.getLastRow() - 1, 3);
  const values = dataRange.getValues();
  values.forEach(row => {
    if (row[0] !== '' && row[2] !== '') {
      row[2] -= row[0];
    }
  });
  sheet.getRange(2, 22, values.length, values[0].length).setValues(values);
}

Processing one row at a time:

function onEdit(event) {
  const editingSheet = event.range.getSheet();
  if (editingSheet.getName() === "CONCRETE" && event.range.rowStart > 1 && event.range.columnStart === 24) {
    let cellValues = editingSheet.getRange(event.range.rowStart, 22, 1, 3).getValues().flat();
    editingSheet.getRange(event.range.rowStart, 24).setValue(cellValues[2] - cellValues[0]);
  }
}

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Eliminate spacing in MaterialUi grids

As I work on a React project, I am faced with the task of displaying multiple cards with content on them. To achieve this layout, I have opted to use MaterialUi cards within Material UI grids. However, there seems to be an issue with excessive padding in t ...

Choose a collection of elements and encase them within a <div> tag

I am currently working on creating a greasemonkey script for a webpage that has a rather challenging structure. My goal is to show and hide different entries, but the content is formatted like this: <a name="first"/> <h3>First</h3> Some ...

Conceal component while navigating in VueJs

I am working on a Vue project. I am trying to implement a feature where a component is hidden while the user scrolls, but then reappears once the scrolling stops. I have tried using a scroll event, but the component does not show up again. <div c ...

React: When state is updated and a console.log is used, the console displays the previous state instead of the updated

Upon clicking the button, a peculiar sequence unfolds - the console displays 0 and the page refreshes to show 1 function App() { const [count, setCount] = useState(0); const addOne = () => { setCount(count + 1) console.log(count) } ...

How to Use Javascript to Listen for Events on a Different Web Page

Is it possible to open a popup using window.open and then subscribe to page events (such as onload) of the popup from the opener? I am looking for a method in my parent page (opener) that will execute when the popup's onload or ready event fires. Can ...

Using multiple html files with ui-router's ui-view feature

Is it possible to create a complex UI using multiple HTML files with ui-view in AngularJS? I am trying to achieve a layout similar to this: I attempted to implement something on Plunker, but it seems like I'm struggling to grasp the underlying concep ...

Is it possible to prevent a webpage from being refreshed?

I need help with an HTML page that puts the worker on pause time which will be subtracted from his/her day job. The issue is that when he/she refreshes the page, the timer starts from the beginning automatically. I want it to continue without resetting. Is ...

Converting SVG with an external PNG file embedded into a standalone PNG format using Node

Does anyone know of a node package that can convert an svg file to a png, including external images embedded within the svg code like this? <?xml version="1.0" encoding="utf-8"?> <svg viewBox="0 0 120 120" height="120" width="120" xmlns="h ...

Updating the parent's data by modifying data in the child component in VueJS

When I use multiple components together for reusability, the main component performs an AJAX call to retrieve data. This data is then passed down through different components in a chain of events. <input-clone endpoint="/api/website/{{ $website->id ...

Issue: ParserError encountered due to a Syntax Error found at line 1, column 32

As a beginner in programming, I am encountering an issue. When I run "npm run build" in the Terminal to compress my project in React.js, I encounter this error. Interestingly, I have previously created another project without facing this problem, and I can ...

Display solely the error message contained within the error object when utilizing the fetch API in JavaScript

I am facing an issue in logging the error message to the console while everything else seems to be working fine. Specifically, I am unable to log only the message such as "email exists" when the email already exists in the system. const submitHandler = a ...

Do I need to use the "--save" flag in npm to add dependencies to the "package.json" file?

Do I really need to use the "--save" flag in order to add an installed dependency to the "package.json" file? I conducted a test without the "save" flag and found that the package was still added to the "dependencies" section. It seems like it is the defa ...

"Troubleshooting: Fixing the 'Firebase Cloud Function admin reference is not a function'

I recently attempted to transform the .WriteOn cloud function in my Firebase app into a scheduled cloud function. The goal was to create a function that would run every 4 days to delete messages older than 2 days. While this worked well for the .WriteOn fu ...

The size of the cursor varies depending on the line it's placed on

I have a content editable div where three lines are separated by BR tags. When I click on the second line, the cursor becomes bigger than that in the first line. .content { line-height: 35px; } <div class="content" contenteditable="true"> ...

What could be causing Jest to prevent me from using the node testing environment, especially when they have made changes to it?

I am currently working on testing a React component within Next.js using Jest. Jest made an official announcement regarding the change of default test environment to Node: Due to this update, the default test environment has been switched from "jsd ...

Tips for utilizing the Toggle Slider JS functionality

I'm attempting to change a value using a slider in HTML, here is my approach: html file : <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <script scr="./scripts.js" ...

sent a data entry through ajax and performed validation using jquery

Is there a solution to validating the existence of an email value using ajax after validation work is completed? Despite attempting to check for the email value and display an alert if it exists, the form continues to submit regardless. See below for the ...

Looking to update the location of an element within a canvas using Vue and socket.io?

I am currently developing a 2D pong game using vue.js and socket.io. At the moment, I have a black rectangle displayed in a canvas. My goal is to make this rectangle move following the cursor of my mouse. The issue I am facing is that although my console l ...

Mistake in Timestamp Separation - JavaScript

I have a Timestamp retrieved from MSSQL and displayed on a Webclient. The timestamp reads: Thu Jan 01 18:00:00 CET 1970, however, I am only interested in the time format: 18:00 or 18:00:00 (preferably the former). <script> var timestamp = ...

Error received - CORS request denied on Firefox browser (Ubuntu)

I encountered a CORS error (CORS request rejected: https://localhost:3000/users) while attempting to register a new user. This issue arose from content in the book Building APIs with node.js, Chapter 12. I am currently using Firefox on Ubuntu and have tr ...