Having difficulty selecting a choice from a dropdown menu

I am hoping to generate a spreadsheet with details on items for my friend and me, such as selling price, cost, and status (Sold, in the sale, etc). I am aiming to automate the "status" column by incorporating dropdown menus with options like "Sold!", "waiting for sale..." and Null. After writing code that changes the status based on specific dates (transitioning to "waiting for sale" if there's an upcoming on-sale date or to "Sold!" if a sold date is present), I encountered difficulties applying it to every row/column.

The script is working correctly when applied to individual rows; however, the actual sheet data remains unchanged. Here is the code:

function AutoStatus() {

  let sheet = SpreadsheetApp.getActive().getSheetByName("Clean");
  let data = sheet.getDataRange().getValues();

    for (n = 0; n < data.length; ++n) {

      let startDate = data[n][1]; 
      let soldDate = data[n][2];
      let status = data[n][6];

        if (startDate != "" && soldDate >= startDate) {
            status = "Sold!";
      }   else if (startDate != "") {
            status = "Waiting for Sale...";
      }   else {
            status = "Null";
      }
    console.log(startDate, soldDate, status)
    }
}

The console log displays the following:

02:10:47 Info Execution Started
02:10:50 Info Start Date Sold Date Status
02:10:50 Info Wed Jun 19 2024 00:00:00 GMT+0200 (Central European Summer Time) '' 'Waiting for Sale...'
02:10:50 Info Thu Jun 20 2024 00:00:00 GMT+0200 (Central European Summer Time) Thu Jun 20 2024 00:00:00 GMT+0200 (Central European Summer Time) 'Sold!'
... 
02:10:48 Info Execution Ended

Despite the console log indicating the correct status changes, the sheet does not reflect these updates (as shown in this Image of the sheet). I have been struggling with this issue for some time now and would greatly appreciate any assistance. Thank you!

javascriptarraysfor-loopgoogle-sheetsgoogle-apps-script

Answer №1

Your sheet iteration process and value updating is spot on, but remember to implement the code for setting the updated values as well:

sheet.getRange().setValue();

Consider integrating the following snippet into your script:

function UpdateStatus() {
  let sheet = SpreadsheetApp.getActive().getSheetByName("Clean");
  let data = sheet.getDataRange().getValues();

  for (let i = 0; i < data.length; ++i) {
    let DMV = data[i][1]; 
    let DV = data[i][2]; 
    let Status = data[i][6]; 

    if (DMV != "" && DV >= DMV) {
      Status = "Sold!";
    } else if (DMV != "") {
      Status = "For Sale...";
    } else {
      Status = "Not Available";
    }
    sheet.getRange(i + 1, 7).setValue(Status); //Adjusting for zero-based arrays.
  }
}

Answer №2

Your current code lacks the ability to push values to the spreadsheet directly. Here is a revised version based on your initial code snippet. It may not be optimized, but it should get the job done. Feel free to reach out if you need assistance in enhancing it further.

It's a good practice to assign the range as a variable, allowing for easy manipulation and updating of values in one go, rather than individual entries.

function UpdateStatus() {

  let sheet = SpreadsheetApp.getActive().getSheetByName("Clean");
  let range = sheet.getDataRange();
  let data = range.getValues();

    for (n = 1; n < data.length; ++n) {

      let DMV = data[n][1]; // Column index starting from 0
      let DV = data[n][2];

        if (DMV != '' && DV >= DMV) {
            data[n][6] = 'Sold!';
      }   else if (DMV != '') {
            data[n][6] = 'For Sale...';
      }  else {
            data[n][6] = 'Null';
      }
    }
  range.setValues(data);
}

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

Index order for a linearly-arranged upper triangular matrix

Given the upper triangular portion of a matrix, offset above the diagonal, stored as a linear array, how can we extract the (i,j) indices of a matrix element from the linear index of the array? For instance, if we have the linear array [a0, a1, a2, a3, a4 ...

c++arraysnumpylinear-algebratriangular

Accessing the parent scope from a directive within a nested ng-repeat in AngularJs

Seeking guidance on accessing the parent scope within a directive nested in an ng-repeat. Here is an example: <div ng-app="myApp" ng-controller="myCtrl"> <div ng-repeat="section in sections"> {{section.Name}} <div ng-rep ...

javascriptangularjs

What's the best way to display alert messages using the alert method in Node.js with Jade?

Is there a way to render a 'Jade view' with data? For example, in the following code: app.get('/', function(req, res){ res.render('alert', {msg: 'hi!'}); }); I attempted to write the Jade code below: (alert.ja ...

javascriptnode.jsexpresspuginterpolation

What is the best way to access the URL of the source page?

How can I retrieve the source page URL when navigating to any page within a specific domain? I've attempted using this jQuery code: $(document).ready(function() { var referrer = document.referrer; }); However, this only returns the previous URL, wh ...

javascriptphpjqueryhtml

Effortlessly add and manipulate multiple classes in a generic class using querySelectorAll and classList, eliminating the

I'm encountering an issue that requires me to repeatedly utilize querySelectorAll with Element.classList. Each time, I must convert the NodeList obtained from Element.querySelectorAll into an Array. Then, I need to iterate over the Array using a for ...

javascriptjqueryarraysselectors-api

Is there a way to connect my host to localhost nodes for access?

Currently, I am immersed in a project based on Ionic. Instead of installing node.js directly onto my machine, I decided to experiment with DDEV, which is primarily known as a PHP development environment. Upon running my application through ionic serve -l, ...

javascriptnode.jsdockernpmddev

Reinvent the AJAX functionality

Is there a way to create a custom function that changes the default ajax functionality? I currently have this ajax function implemented: $.ajax({ type: "POST", url: "http://" + document.location.host + '/userajax', data: 'type= ...

javascriptjqueryajax

Enhancing Vue JSX functionality: Tips and strategies

When working with JSX in Vue, it requires creating a dedicated file, whereas in React, we can use it inline within JavaScript. Is there a way to achieve this in Vue? Contents of .babelrc : { "presets": [ "@babel/preset-react&quo ...

javascriptvue.jsjsx

Utilizing React and Material-UI to Enhance Badge Functionality

I am exploring ways to display a badge icon when a component has attached notes. I have experimented with three different methods and interestingly, the results are consistent across all of them. Which approach do you think is the most efficient for achiev ...

javascripthtmlcssreactjsmaterial-ui

Having trouble with my Express app due to errors popping up because of the order of my routes

app.get('/campgrounds/:id/edit', async (req,res) =>{ const campground = await Campground.findById(req.params.id) res.render('campgrounds/edit', { campground }); }) app.get('/campgrounds/:id', async (req,res) =>{ ...

javascriptnode.jsmongodbexpressmongoose

What is the correct way to retrieve a response from an async/await function?

After working with async/await functions for a few months, I have managed to get my code functioning. However, there are still some aspects of it that elude me, and I could benefit from some guidance. Within the code snippet below lies an async function t ...

javascriptreactjsasync-await

Tips for implementing fetch in object-oriented programming

Recently, I've been diving into the world of Object-Oriented Programming (OOP) and trying to utilize fetch for data retrieval. Unfortunately, I've hit a snag when it comes to passing information between classes. I'm struggling to successfull ...

javascriptclassoopfetch

Is the behavior of undefined different in Chrome?

Upon examining my Asp masterpage, I noticed the following code snippet: <script> if (theForm !== undefined) { // <<== line 746: error theForm.onsubmit = ...bla bla... ; } </script> When checking the Chrome console, an er ...

javascriptasp.netgoogle-chromeundefined

Function returns to execute another Function

Update: Is there a way to retrieve a value from a nested function and have it returned by the parent function? function returningFn() { function otherFn() { var value = 123; return value; //To be retrieved by returningFn } } I nee ...

javascriptjquery

Why is jQuery ajax not functioning in the view file in a node.js environment?

I have recently started working with Express Node.js and I am facing an issue with making an AJAX call or using jQuery from the HBS view. Below are my controller methods: router.get('/', function(req, res) { console.log("home get request"); ...

javascriptnode.jsexpress

Tips for incorporating tool-tips into a vertical grouped bar chart using d3 js

I have successfully created a simple vertical grouped bar chart using the code provided below. Now, I want to enhance it by adding tooltips that display specific data about each bar when hovered over. Can anyone assist me with this? Any help is greatly app ...

javascriptcssd3.jstooltip

Converting long date format to short date format: yyyy-mm-dd

Is there a way to convert the long date format from "Sat Dec 13 2014 06:00:00 GMT+0600" to "2014-12-13" in yyyy-mm-dd format? <script> function myDate(val) { var now = new Date("<?php echo $Cnextdue;?>"); now.setDate(now.getDate() + 30*val); ...

javascriptdatedatetime

Is Joomla.submitbutton('module.apply') causing the page to refresh?

The <em>Joomla.submitbutton('module.apply')</em> function causes the page to reload. I attempted to use it with ajax, but the page still reloads. Is there a way to utilize this function with ajax without refreshing the page? Thank yo ...

javascriptjqueryajaxjoomla

Avoiding HTML in JavaScript: Tips and Tricks

My application generates numerous elements dynamically based on server data in JSON format. This process involves embedding a significant amount of HTML directly within my JavaScript code, leading to pollution and making it increasingly challenging and l ...

javascriptjqueryhtmlajax

Having issues with Json stringification and serializing arrays

Having an issue with Json when using serializeArray. An example of my HTML form: <form action="" method="post" name="myForm"> ID: <input type="text" name="id" /><br/> State (XX): <input type="text" name="state" /><br/> <p ...

javascripthtmljsonserializationforms