How do I add a value to a specific column in a Google Sheet?

414 views
Skip to first unread message

Dan Konig

unread,
Jun 15, 2020, 3:35:13 PM6/15/20
to Google Apps Script Community
Adding a value to a the last row of a spreadsheet but adding a value to the end of a specific column seems to be elusive to me. How would you do this? 

I posted this over in StackOverflow as well. You can see a lot more detail there. I'd be happy to post it here as well if necessary.

Michael O'Shaughnessy

unread,
Jun 15, 2020, 9:28:47 PM6/15/20
to google-apps-sc...@googlegroups.com
Need a little more explanation... Your code on StackOverflow looks like you are trying to add data to the last row on a spreadsheet, just like when a Google Form is submitted.  However, you say "not all columns are of equal length"... My question is, why?  

If you are trying to use the spreadsheet as a "database" then you should be appending rows.  Your "addRecord" function looks to be trying to emulate how a Google Form appends data.  If you are missing data for a column, no big deal.  You will just have to handle that when you try to read the values.



--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3a7884f1-5a9f-4cb7-99f8-83b1703c575ao%40googlegroups.com.

Dan Konig

unread,
Jun 16, 2020, 8:39:16 AM6/16/20
to Google Apps Script Community
@mro.michael The code I posted was how I am adding a record to a sheet. That is indeed being used as a database. What I am now trying to do is add a value to the end of a column on another sheet which serves as the data source for my various dropdowns. Not all dropdowns have the same number of values hence the reason they don't have the same number of rows.
 Make sense? Maybe sharing the code I am using to add a record is not the best choice but I envisioned the add to column being similar in some ways.


On Monday, June 15, 2020 at 9:28:47 PM UTC-4, mro.michael wrote:
Need a little more explanation... Your code on StackOverflow looks like you are trying to add data to the last row on a spreadsheet, just like when a Google Form is submitted.  However, you say "not all columns are of equal length"... My question is, why?  

If you are trying to use the spreadsheet as a "database" then you should be appending rows.  Your "addRecord" function looks to be trying to emulate how a Google Form appends data.  If you are missing data for a column, no big deal.  You will just have to handle that when you try to read the values.



On Mon, Jun 15, 2020 at 3:35 PM Dan Konig <dan....@connection.com> wrote:
Adding a value to a the last row of a spreadsheet but adding a value to the end of a specific column seems to be elusive to me. How would you do this? 

I posted this over in StackOverflow as well. You can see a lot more detail there. I'd be happy to post it here as well if necessary.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Dan Konig

unread,
Jun 16, 2020, 2:02:06 PM6/16/20
to google-apps-sc...@googlegroups.com
Here's some code around where I am at now. This does successfully update the spreadsheet column as intended but fails when updating the dropdown. I'm probably doing something wrong here. Here is the JS.

function addProduct() {
    let newProd = document.getElementById("newProduct").value;
         
    google.script.run.withSuccessHandler(updateProductDropdown).addNewProduct(newProd);
         
    document.getElementById("newProduct").value = "";
}
       
function updateProductDropdown(newProd){
    var newOption = document.createElement("option");
    newOption.innerText = newProd;
    document.getElementById("product").append(newOption);

}

Here is the GS which you can see over on SO (more or less)

function addNewProduct(newProd){
    var columnLetterToGet, columnNumberToGet, direction, lastRow, lastRowInThisColWithData, rng, rowToSet, startOfSearch, valuesToSet;
    var ss = SpreadsheetApp.openById(ssId);
    var ws = ss.getSheetByName("List Source");

    lastRow = ws.getLastRow();
 
    columnNumberToGet = 2;
    columnLetterToGet = "B";

    startOfSearch = columnLetterToGet + (lastRow).toString();

    rng = ws.getRange(startOfSearch);

    direction = rng.getNextDataCell(SpreadsheetApp.Direction.UP);

    lastRowInThisColWithData = direction.getRow();
 
    rowToSet = lastRowInThisColWithData + 1;
    valuesToSet = [newProd];

    ws.getRange(rowToSet, 9).setValues([valuesToSet]);
}

Like I said this writes the new value to the spreadheet column perfectly I just can't get it to add the new value to the dropdown. To me it seems like there should be some type of refresh for that one dropdown.

The dropdown gets populated from the GS like this

    <select id="product" onchange="buildURL()">
        <option disabled selected value="">Choose a product</option>
               
            <?!= productList; ?>

    </select>

The GS is pulling the data from the Google Spreadsheet.

Michael O'Shaughnessy

unread,
Jun 16, 2020, 11:02:02 PM6/16/20
to google-apps-sc...@googlegroups.com
Ahhhh... I understand now...

I will take a closer look but in the meantime have a look at this video:

He does a great job of showing how to fill dropdowns.

On Tuesday, June 16, 2020 at 8:39:16 AM UTC-4, Dan Konig wrote:
@mro.michael The code I posted was how I am adding a record to a sheet. That is indeed being used as a database. What I am now trying to do is add a value to the end of a column on another sheet which serves as the data source for my various dropdowns. Not all dropdowns have the same number of values hence the reason they don't have the same number of rows.
 Make sense? Maybe sharing the code I am using to add a record is not the best choice but I envisioned the add to column being similar in some ways.


On Monday, June 15, 2020 at 9:28:47 PM UTC-4, mro.michael wrote:
Need a little more explanation... Your code on StackOverflow looks like you are trying to add data to the last row on a spreadsheet, just like when a Google Form is submitted.  However, you say "not all columns are of equal length"... My question is, why?  

If you are trying to use the spreadsheet as a "database" then you should be appending rows.  Your "addRecord" function looks to be trying to emulate how a Google Form appends data.  If you are missing data for a column, no big deal.  You will just have to handle that when you try to read the values.



On Mon, Jun 15, 2020 at 3:35 PM Dan Konig <dan....@connection.com> wrote:
Adding a value to a the last row of a spreadsheet but adding a value to the end of a specific column seems to be elusive to me. How would you do this? 

I posted this over in StackOverflow as well. You can see a lot more detail there. I'd be happy to post it here as well if necessary.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cc8776fe-e23c-46b8-be68-a7a3f43babf7o%40googlegroups.com.

Dan Konig

unread,
Jun 17, 2020, 5:12:11 PM6/17/20
to Google Apps Script Community
Cool. I've built a lot of this app off what he has on his channel. This particular situation is not really covered.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Michael O'Shaughnessy

unread,
Jun 18, 2020, 11:51:12 PM6/18/20
to google-apps-sc...@googlegroups.com
Ok, hopefully I have a working example for you to look at... 
Here is the link to the webapp:

And here is a link to a shared folder that has the spreadsheet and the Script file:

This is just a simple webpage that has a dropdown and an input box.  The dropdown is loaded from a list on the "options" tab of the spreadsheet.  When you make a selection, enter something in the box then click the button the data is saved to the spreadsheet and the input box data is added to the bottom of the options on the options tab.  Then the dropdown is refreshed to include the new options.

Hope this helps!


To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2a411553-9bf2-4be0-9496-4da1b6a0d221o%40googlegroups.com.

Dan Konig

unread,
Jun 22, 2020, 11:32:02 AM6/22/20
to Google Apps Script Community
This is pretty helpful, and it works, but I believe I would need to rebuild a good part of my app to implement this. You don't think what I posted earlier can be updated to work?
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Dan Konig

unread,
Jun 22, 2020, 6:04:16 PM6/22/20
to Google Apps Script Community
A quick update. All of the code I posted earlier in this thread works. Things fall down when I get to the updateProductDropdown function. Stripping it back and just popping an alert validates that the function is indeed getting the new value. 

function updateProductDropdown(newProd){
    alert(newProd);
}

Trying to append that value to the dropdown list doesn't work though. This might have to do with the fact I am using Materialize CS for the fields. In another forum one use believes I need to re-instantiate the select. Here is the codepen he provided. https://codepen.io/doughballs/pen/LYGxEwb. If that is the case then maybe returning `newProd` to the `updateProductDropdown` function might not be the way to go. Instead I should use `updateProductDropdown` to reinstantiate the select. Just not sure how to achieve that. Any ideas?

Dan Konig

unread,
Jun 22, 2020, 7:17:24 PM6/22/20
to Google Apps Script Community
Let me take that last part back. I can append the new value to the select like this

function updateProductDropdown(newProd){
          newProdOption = document.getElementById('product');
          newProdOption.innerHTML += '<option>' + newProd + '</option>';
          alert(newProdOption.innerHTML)
       }

However, while the alert shows the new option added, it does not appear in the select. I need a way to refresh it which I think circles back to re-instantiating it. If I di that I don't think I need to add the new value in code. The select would just pull the values, including the new one, from the spreadsheet.....I think.

Alan Wells

unread,
Jun 22, 2020, 7:41:19 PM6/22/20
to Google Apps Script Community
Try this:

function updateProductDropdown(newProd){
  var selectTag = document.getElementById("product");

  var newOption = document.createElement("option");
 
  newOption.text = newProd;
  newOption.value = newProd;
  selectTag.options.add(newOption);

}

Michael O'Shaughnessy

unread,
Jun 22, 2020, 9:51:21 PM6/22/20
to google-apps-sc...@googlegroups.com
OK, maybe I missed the Materialize css....
In ONE practice web app that I used Materialize I had to make sure text boxes and other elements were "initialized" and had to be "re initialized" when things were changed (for example info was submitted)

Here is the linke to Materialize docs and you want to scorl down to "Initialization":  https://materializecss.com/select.html

Here is the text : "You must initialize the select element as shown below. In addition, you will need a separate call for any dynamically generated select elements your page generates."  So when you are ready to "add" the option you will need to "re initialize" the select.

And at about 12 minutes into this video: https://www.youtube.com/watch?v=yu-nNEPw83k&t=24s he discusses the Materialize select component as well as initializing it.

Hope this helps.



To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/dcf0fa8b-44ae-4a3e-98bb-4e17231c32a5o%40googlegroups.com.

Dan Konig

unread,
Jun 23, 2020, 7:40:57 AM6/23/20
to Google Apps Script Community
Right. I built my app largely from his videos and have the initialization script in place for that initial load. I'm just not sure how to re-initialize and only re-initialize the one select without refreshing the entire page if that makes sense.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Dan Konig

unread,
Jun 23, 2020, 7:49:05 AM6/23/20
to Google Apps Script Community
Pretty much does the same thing as the last code I posted. It does add the new option in the code but the select does not render it. I believe I need to re-initialize that select to show the new option. If that is the case I am not even sure I need to add the option directly though code. The re-initialization may pull all of the values from the spreadsheet like it does when the app first loads. If it does I am all set since my `withSuccessHandler` function adds the new value to the spreadsheet.

dan....@connection.com

unread,
Jun 24, 2020, 9:24:29 AM6/24/20
to Google Apps Script Community
Turns out this bit of code did the trick. It's odd because I was pretty sure I tried this before.
`function updateProductDropdown(newProd){
           newProdOption = document.getElementById('product');
           newProdOption.innerHTML += '<option>' + newProd + '</option>';
           var elems = document.querySelectorAll('select'); 
           var instances = M.FormSelect.init(elems);
       }`

Reply all
Reply to author
Forward
0 new messages