Google Sheet Script

[Matt Eichman]

Hi,

I am looking for help in writing a script for a Google Sheet that I have set up in tracking some health and exercise data in dealing with a physical condition. The Google Sheet is shared with a health provider so they can see my progress in between meetings.

I tried using scripts language suggested from AI in the Google Search, but it is not working and I do not know enough to figure out why. So, here I am seeking help from those that do. I have tried searching for answers, but have still not been able to complete it. Any help is appreciated in advance.

Here are the parameters of what i am trying to accomplish:

1. I have a single Sheet with different tabs that track different types of information. Most of them are easy enough to enter manually as there is only one table.

2. One tab is used to track multiple exercises, stretches, and movements that are rotated through to manage and improve the condition. The tab is named Movements.

3. Each item is set up in an identical table format. There are nine columns. The first row is merged with the name of the movement. The second row has the column headings of Date, Round 1, Count 1, Round 2, Count 2, Round 3, Count 3, Round 4, Count 4.

4. I named the range for each table. Taking in fact that the first row of the tab is blank and everything starts on Row 2, the range Movement1 is defined as 'Movements'!D2:L1000, then a column space, then 'Movements'!N2:V1000, then so on.

5. The tab before Movements is called Schedule. This is where I was trying to place a bound script to create entries.

6. The goal was to have an entry line on Schedule eleven columns wide. The first cell would be a dropdown list of the named ranges. Cells 2-10 would be the nine pieces of information listed in point 3. Cell 11 would be a button to press that would activate the script. The script would then add the information in a new line in the correct table referenced in the dropdown list.

Is this possible? As a bonus feature, is it possible to have a second script that recalls the most recent line entry from a table?

Hoping someone takes interest.

Thank you.

[SMAARTE Group]

Matt,

I copied and pasted your email into Gemini 3.1 Pro.  You should get an AI subscription.  It will probably save you time and money.  Here's my prompt and the solution (which I encourage you to improve upon):

take a look at the email message below to a google apps script email forum and provide a one shot solution based on the description making reasonable assumptions about the sheet configuration

How to advise the user to implement this:

1. Paste the code: Go to Extensions > Apps Script, clear out any existing code, paste the code above, and click the Save (floppy disk) icon.

2. Assign the buttons: * On the Schedule tab, insert a drawing or image to act as your "Save" button (in column K or L).

   • Right-click the button, click the three vertical dots, select Assign script, and type exactly: addMovementEntry

   • Optional Bonus: Insert a second button for recalling data, and assign the script: recallLastEntry

3. Permissions: The first time you click the button, Google will ask for authorization. Follow the prompts (Click Continue -> Choose your account -> Click "Advanced" -> Click "Go to script").

Why previous AI attempts likely failed the user: Most AI tools default to sheet.getLastRow() when appending data. If you have side-by-side tables (like D2:L1000 and N2:V1000), getLastRow() calculates the bottom of the deepest table on the entire sheet. This script fixes that by pulling the values of the specific named range and looping through its "Date" column until it finds a blank cell, isolating the tables from one another perfectly.

/** Main function to add a new movement entry from the 'Schedule' tab

 * to the appropriate table on the 'Movements' tab.

 * Assign this function to your "Save" button.

 */

function addMovementEntry() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var scheduleSheet = ss.getSheetByName("Schedule");

 

  // Assuming the entry line is on Row 2.

  // A2 is the dropdown, B2:J2 are the 9 data points.

  var entryRange = scheduleSheet.getRange("A2:J2");

  var entryValues = entryRange.getValues()[0];

 

  var targetRangeName = entryValues[0]; // The named range from the dropdown

  var dataToLog = entryValues.slice(1); // The 9 data points

 

  // Validation: Check if a movement is selected

  if (!targetRangeName || targetRangeName === "") {

    SpreadsheetApp.getUi().alert("Please select a Movement from the dropdown in column A.");

    return;

  }

 

  // Validation: Check if at least a date is entered

  if (dataToLog[0] === "") {

    SpreadsheetApp.getUi().alert("Please enter at least a Date for the entry.");

    return;

  }

  // Get the target named range

  var targetRange = ss.getRangeByName(targetRangeName);

  if (!targetRange) {

    SpreadsheetApp.getUi().alert("Could not find the table named '" + targetRangeName + "'. Check your named ranges.");

    return;

  }

 

  // Find the next empty row within this specific named range

  var rangeValues = targetRange.getValues();

  var nextEmptyRowIndex = -1;

 

  // We start the loop at index 2 (which is the 3rd row of the named range)

  // because index 0 is the Title, and index 1 is the Column Headers.

  for (var i = 2; i < rangeValues.length; i++) {

    if (rangeValues[i][0] === "") { // Checking the "Date" column to see if it's empty

      nextEmptyRowIndex = i;

      break;

    }

  }

 

  if (nextEmptyRowIndex === -1) {

    SpreadsheetApp.getUi().alert("The table for " + targetRangeName + " is full! Please expand the named range.");

    return;

  }

 

  // Write the data to the correct row and columns

  // targetRange.getRow() gets the absolute starting row (e.g., Row 2)

  var writeRow = targetRange.getRow() + nextEmptyRowIndex;

  var writeCol = targetRange.getColumn();

  var writeSheet = targetRange.getSheet();

 

  writeSheet.getRange(writeRow, writeCol, 1, 9).setValues([dataToLog]);

 

  // Clear the input fields on the Schedule sheet (B2:J2), leaving the dropdown intact

  scheduleSheet.getRange("B2:J2").clearContent();

 

  // Show a success message

  ss.toast("Entry added successfully to " + targetRangeName + "!", "Success", 3);

}

/** Bonus function: Recalls the most recent entry from the selected table

 * and populates it back into the 'Schedule' tab.

 * Assign this function to a "Recall Last" button.

 */

function recallLastEntry() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var scheduleSheet = ss.getSheetByName("Schedule");

 

  // Get the selected movement from the dropdown in A2

  var targetRangeName = scheduleSheet.getRange("A2").getValue();

 

  if (!targetRangeName || targetRangeName === "") {

    SpreadsheetApp.getUi().alert("Please select a Movement in column A to recall.");

    return;

  }

 

  var targetRange = ss.getRangeByName(targetRangeName);

  if (!targetRange) {

    SpreadsheetApp.getUi().alert("Could not find the table named '" + targetRangeName + "'.");

    return;

  }

 

  var rangeValues = targetRange.getValues();

  var lastUsedRowIndex = -1;

 

  // Find the last row that HAS data

  for (var i = 2; i < rangeValues.length; i++) {

    if (rangeValues[i][0] === "") {

      lastUsedRowIndex = i - 1; // The row right before the empty one

      break;

    }

  }

 

  // If lastUsedRowIndex is 1, it means only the headers exist

  if (lastUsedRowIndex <= 1) {

    SpreadsheetApp.getUi().alert("There are no entries logged for " + targetRangeName + " yet.");

    return;

  }

 

  // Extract the data from the last used row

  var lastData = rangeValues[lastUsedRowIndex];

 

  // Write the data back to the Schedule entry line (B2:J2)

  scheduleSheet.getRange("B2:J2").setValues([lastData]);

 

  ss.toast("Last entry for " + targetRangeName + " has been recalled.", "Success", 3);

}

Regards,

Steve Horvath

SMAARTE Group

--
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 visit https://groups.google.com/d/msgid/google-apps-script-community/35a09b1f-6b97-46f4-8c59-d5a089ed54e2n%40googlegroups.com.

[Matt Eichman]

Thank you very much, this worked and helps considerably.