OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Struggling to set an option in a google Sheet's dropdown menu with JS

  • Thread starter Thread starter Clean Pick
  • Start date Start date
C

Clean Pick

Guest
I am trying to create a sheet for my friend and I about some items selling, cost and state (Sold, in sale, etc) and i want to automate my "state" column, which contains dropdown menus with "Sold !", "waiting for sale..." and "Null" options. I succeed writing the code to change the state depending on the date (if there's an on-sale date then the option turns to "waiting for sale" and if there's a sold date the option changes to "Sold !".

But i'm struggling to aplly the script to every row/column. I can get the console.log to get what i want but not the actual sheet.

Here's my code :

Code:
function EtatAuto() {

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

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

      let DMV = data[n][1] ; // x is the index of the column starting from 0
      let DV = data[n][2] ;
      let E = data[n][6]  ;

        if ( DMV != "" && DV >= DMV) {
            E = "Vendu !";
      }   else if (DMV != "") {
            E = "En Vente...";
      }   else {
            E = "Null";
      }
    console.log(DMV, DV, E)
    }
}

The console.Log is returning :

Code:
02:10:47    Avis    Exécution démarrée
02:10:50    Infos   Date mise en vente Date de vente En Vente...
02:10:50    Infos   Wed Jun 19 2024 00:00:00 GMT+0200 (Central European Summer Time) '' 'En Vente...'
02:10:50    Infos   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) 'Vendu !'
02:10:50    Infos   Thu Jun 20 2024 00:00:00 GMT+0200 (Central European Summer Time) Fri Jun 21 2024 00:00:00 GMT+0200 (Central European Summer Time) 'Vendu !'
02:10:50    Infos   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) 'Vendu !'
02:10:50    Infos   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) 'Vendu !'
02:10:50    Infos   Fri Jun 21 2024 00:00:00 GMT+0200 (Central European Summer Time) '' 'En Vente...'
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:50    Infos     Null
02:10:48    Avis    Exécution terminée

But the sheet doesn't change at all (but it does when i do it in a single row without the "for" :Image of the sheet (spoiler : everything is Null because i set them that way so but they are not updating)

I'm stuck on this for far too long on it and i am seeking for your help, thanks !

<p>I am trying to create a sheet for my friend and I about some items selling, cost and state (Sold, in sale, etc) and i want to automate my "state" column, which contains dropdown menus with "Sold !", "waiting for sale..." and "Null" options. I succeed writing the code to change the state depending on the date (if there's an on-sale date then the option turns to "waiting for sale" and if there's a sold date the option changes to "Sold !".</p>
<p>But i'm struggling to aplly the script to every row/column. I can get the console.log to get what i want but not the actual sheet.</p>
<p>Here's my code :</p>
<pre><code>function EtatAuto() {

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

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

let DMV = data[n][1] ; // x is the index of the column starting from 0
let DV = data[n][2] ;
let E = data[n][6] ;

if ( DMV != "" && DV >= DMV) {
E = "Vendu !";
} else if (DMV != "") {
E = "En Vente...";
} else {
E = "Null";
}
console.log(DMV, DV, E)
}
}
</code></pre>
<p>The console.Log is returning :</p>
<pre><code>02:10:47 Avis Exécution démarrée
02:10:50 Infos Date mise en vente Date de vente En Vente...
02:10:50 Infos Wed Jun 19 2024 00:00:00 GMT+0200 (Central European Summer Time) '' 'En Vente...'
02:10:50 Infos 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) 'Vendu !'
02:10:50 Infos Thu Jun 20 2024 00:00:00 GMT+0200 (Central European Summer Time) Fri Jun 21 2024 00:00:00 GMT+0200 (Central European Summer Time) 'Vendu !'
02:10:50 Infos 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) 'Vendu !'
02:10:50 Infos 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) 'Vendu !'
02:10:50 Infos Fri Jun 21 2024 00:00:00 GMT+0200 (Central European Summer Time) '' 'En Vente...'
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:50 Infos Null
02:10:48 Avis Exécution terminée
</code></pre>
<p>But the sheet doesn't change at all (but it does when i do it in a single row without the "for" :<a href="https://i.sstatic.net/7AlRWWNe.png" rel="nofollow noreferrer">Image of the sheet</a> (spoiler : everything is Null because i set them that way so but they are not updating)</p>
<p>I'm stuck on this for far too long on it and i am seeking for your help, thanks !</p>
 

Latest posts

M
Replies
0
Views
1
Muhammad Rafli Aditya. H
M
Top