google-apps-scripttriggersgoogle-sheets

Button to update Row

I am not a big fan of some of the Stock Broker’s GUI, so I am attempting at tracking some of my assets on a Google Sheet.

How can I create Manual (click a button) or Automatic triggers, based off of a certain date, to edit a cell's value or add a new entry into a new cell?

Example Triggers:

  1. Increment currently owned shares on the Dividend Payout Date.
  2. Nightly/Weekly new entries for Profit/Loss for a company/set of companies.

Sample Sudo Logic:

  1. When TODAY() == 2/2/2018(any variable date saved in a cell), update cell [current shares] with reinvested_shares + current_shares.
  2. Create a new entry in next available cell with profit/loss for the day. New entry would be static and never changing. This could be a button to manually trigger or automatic.

Since you won't have the spreadsheet that contains the cell information, any sample template (with comments) would be highly appreciated.

Solution

  • After much trial and error, I was able to create a manual button to update and insert a new row into a sheet.

    Example:

    enter image description here

    Goal is to copy/save the values of Row 2, create a new row underneath, and place the values into the newly created Row 3 without modifying any other values below.

    Insert->Drawing->Shapes

    Draw a Box, label it as "Update".

    Tools->Script Editor

    Paste/Save:

    function updateInfo() 
{
  var date = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var stats_sheet = ss.getSheets()[1];
  stats_sheet.insertRowBefore(3);

  var todays_cells = SpreadsheetApp.getActiveSheet().getRange("A2:D2").getValues();
  var update_todays_cells = stats_sheet.getRange("A3:D3");
  update_todays_cells.setValues(todays_cells);
}

    Right-Click the Box, click the 3 dots, assign to script->"updateInfo" (without the quotes)

    Clicking the button will result in this: enter image description here