Javascript Question posted in
A very good W3school tutorial can be found here.

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

I am trying to create a sheet for my friend and me about some items selling, cost, and state (Sold, in the sale, etc) and I want to automate my "state" column, which contains dropdown menus with "Sold !", "waiting for sale…" and Null options. I succeeded in 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 apply 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 :

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 :

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 !

Tags:

2

Answers


  1. 0

    You don’t have any code that’s pushing values to the spreadsheet itself. You can try something like this. I didn’t optimize it, I just retooled what you listed in your question, but it should work. Let me know if you want any help iterating through improving it.

    I generally set the range as its own variable, so I can use it to set values to that range in the spreadsheet in a single action (this is preferred over writing individual values to the spreadsheet).

    function EtatAuto() {

  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]; // x is the index of the column starting from 0
      let DV = data[n][2];

        if ( DMV != '' && DV >= DMV) {
            data[n][6] = 'Vendu !';
      }   else if (DMV != '') {
            data[n][6] = 'En Vente...';
      }  else {
            data[n][6] = 'Null';
      }
    }
  range.setValues(data);
}
    Login or Signup to reply.
  2. 0

    You have iterated correctly through your sheet and updating the values accordingly, But you need to set the updated values via code:

    sheet.getRange().setValue();

    Try adopting below into your code:

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

  for (let n = 0; n < data.length; ++n) {
    let DMV = data[n][1]; 
    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";
    }
    sheet.getRange(n + 1, 7).setValue(E); //Set the updated values n+1 to accomodate the fact that Arrays are Zero based.
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search