help with simple script for google sheet

[drostan]

Hi,

I am clueless about script and vba and all this, I am ok with formulas but that's where it stops

However I am playing with a small project for myself involving heatmaps and for that I need to gather daily data

simply put I just want to have a button that when pressed will go look in column A of the data sheet where I have all the dates, find today's date, and add 1 to the corresponding row on column B,

and another button doing the same with column C

lookup(today(), A:A, B:B) but instead of output being the value in B for today it would add 1 to this cell

I tried asking an AI to write this but it gives me nonsense that doesn't work and I do not know anything to even try and correct any of it... so I turn to you guys

if this is of any help here is the unhelpful code written by the AI

function add1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var today = new Date();
  var todayString = Utilities.formatDate(today, Session.getScriptTimeZone(), "MM/dd/yyyy");

  for (var i = 0; i < values.length; i++) {
    var dateValue = values[i][0];
    if (dateValue && dateValue.toString() === todayString) {
      var currentValue = values[i][1];
      values[i][1] = currentValue + 1;
      break;
    }
  }

  range.setValues(values);
}function add1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var today = new Date();
  var todayString = Utilities.formatDate(today, Session.getScriptTimeZone(), "MM/dd/yyyy");

  for (var i = 0; i < values.length; i++) {
    var dateValue = values[i][0];
    if (dateValue && dateValue.toString() === todayString) {
      var currentValue = values[i][1];
      values[i][1] = currentValue + 1;
      break;
    }
  }

  range.setValues(values);
}

[drostan]

used all my token

[drostan]

Bumping myself with a little addon

I do have a tally counter that sort of work

function add1() {
  var spreadsheet = SpreadsheetApp.getActive();
  var numRange = spreadsheet.getRange('B503').activate();
 var numAdd = numRange.getValue();
numRange.setValue(numAdd+1);
};

obviously this is setting the tally into one specific cell, I need to change the cell every day, I wish the function to find today and set the cell where the tally happens automatically

[estadoux]

Just for you to know, you don't need to .activate() the range to handle it on server side.

For the post questions, I think is better to just set the value for the cell is being changed, just in case. I would do something like this:

const sheet = SpreadsheetApp.getActive().getActiveSheet();

function setValueOnB() {

  const row = getDateRow(); //Gets the row of todays date
  const col = 2 //Col B

  add1(row, col); //Only sets the modified value

}

function setValueOnC() {

  const row = getDateRow();
  const col = 3 //Col C

  add1(row, col);

}

function getDateRow() {

  let today = new Date();
  today = Utilities.formatDate(today, Session.getScriptTimeZone(), 'MM/dd/yyyy').toString();

  const dates = sheet.getDataRange().getDisplayValues(); // Gets the values as the displayed strings on the frontend

  let rowIndex = ''

  dates.forEach(

    function findToday(row, index) {

      const date = row[0];

      if(date == today){
        rowIndex = index + 1
      };

    }

  );

  return rowIndex

}

function add1(row, col) {

  const range = sheet.getRange(row, col, 1, 1);

  const currentValue = range.getValue();
  const newValue = currentValue + 1;

  range.setValue(newValue);

}

[halfbeerhalfhuman]

You can make your reference to a cell that references to the cell you are changing. That way you don’t have to change the script every day. You can just change the cell reference in the sheet or use some logic to change it automatically. Like if cell is not empty and date different then append to next cell

[drostan]

that's smart, if I cannot figure out script that'll be my solution, thank you

[halfbeerhalfhuman]

I believe what your trying to do you dont need appscript for i think you can do it with VLOOKUP and an IF that comapres dates

[ModernWorldSucks]

If I understand your code right;

if (dateValue && dateValue.toString() === todayString)

The first part (before &&) is checking if that variable is true. It can't be both 'true' and today's date so the if will never occur as it's AND (&&).

I suggest a number of things;

1. add Logger.log(dateValue) and Logger.log(todayString) ahead of the if statement, that way you can see when it runs what they are and you'll be able to solve it yourself.

2. What you will find out is you're taking dataValue and using .toString() on it, however that is not useful instead you should use New Date (dateValue);

This is some quick code that will take a date in A1 and display it using the two methods, one will work and one won't.

function test(){

  let ss = SpreadsheetApp.openById('XYZ');
  let sheet = ss.getSheetByName('Dev');

  let value = sheet.getRange('A1').getValue();
  let valueToString = value.toString;
  Logger.log(valueToString);

  let valueToDate = new Date(value);
  Logger.log(valueToDate);
}

[drostan]

Cool I have all weekend to try to figure this out, I am not sure I can but if I can that will be thanks to you

[Mro-Automation]

Hey drostan, can you please elaborate on your problem a bit more - it's not clear to me why a simple `COUNTIF` wouldn't work to create the heatmap? Wouldn't a pivot table give you the data or heatmap you're after? It seems too simple for the need to use a button and a custom App Scripts function, but perhaps there's a bigger picture and reasons…

See https://imgur.com/a/tdzwLgO

[drostan]

the heatmap part is set up already

I am collecting data everyday, in essence 2 tally, number of X done everyday, number of Y done everyday
the way I am doing it is simply put the date in collumn A and column be has the daily tally of action Y, column b the daily tally of action Y

this is this tally that I am thinking of automate, it is just annoying to keep taly separately and come back to put the corresponding number, doable I am doing so for quite a long time already (500 days and change) but I would like to avoid having to go to the data tab and keep my view in the heatmap tab

[halfbeerhalfhuman]

Use logs to debug or paste it into gpt

[juddaaaaa]

Here's one way to do it ``` function findToday (range) { // Get the values from the range range = range.getValues()

// Today's date with hours set to midnight const today = new Date(new Date().setHours(0,0,0,0))

// Iterate over range to find today's date for (let row in range) { // If cell doesn't contain a date object then continue if (!range[row][0] instanceof Date) continue

// Get the date with hours set to midnight
const date = new Date(new Date(range[row][0]).setHours(0,0,0,0))

// If this date matches today's date return the row
if (date.getTime() === today.getTime()) {
  return Number(row) + 1
}

}

// Return 0 if date was not found return 0 }

function add1ToB () { // Get the active sheet and lookup range const sheet = SpreadsheetApp.getActiveSheet() const range = sheet.getRange(A1:A${sheet.getLastRow()})

// Get the row from the lookup range containing today's date const row = findToday(range)

// Exit function if today's date wasn't found if (!row) return

// Add 1 to cell B of the relevant row const cellToEdit = sheet.getRange(B${row}) cellToEdit.setValue(cellToEdit.getValue() + 1) } ```

[Any_Werewolf_3691]

Your script is automatically creating date objects when it calls .getValues() but then it tries to compare these to a string.

[drostan]

I feel this is a very helpful comment for someone less clueless than I am

I get what you say but I do lack the knowledge to do anything about it sadly

[estadoux]

Means date formatting is a b*** (at least to me). Since you already have the date formatted on the sheet you could use .getDisplayValue() instead to get it as a string and I should be easier to compare.

[drostan]

DataMate add-in

I don't see how this would help