CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsautomation
gg0wwrr1
gg0wwrr1

Reputation: 21

How to get dynamic values in a cell?

I have a column called "order number" in a sheet. At the start of a day, the value in that cell would be 1. How do I make something that will set the value that every time the cell is cleared, the number increments by 1?

Upvotes: 0

Views: 711

Answers (1)

Nami888
Nami888

Reputation: 247

I assume you mean a cell as A1. You may want to use B1 as a store cell and coloring the text white (hide your storage value/number of edits).

I built a sample here: https://docs.google.com/spreadsheets/d/1LUT58HIr3GGvzQ6qlUUGzmKei3yA2vIdxuwrv9FAq_8/edit#gid=0. Try clearing A1 and u will see it increased.

// To increment by 1 every time cell is cleared
function onEdit(){
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const edit = ss.getRange('A1').getValue();
  const store = ss.getRange('B1').getValue();
  
  if (edit != '') return;
  var newstore = store + 1;
  ss.getRange('B1').setValue(newstore);
  ss.getRange('A1').setValue(newstore);
}

// To refresh the value to 1 daily
function refresh(){
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  ss.getRange('B1').setValue('1');
  ss.getRange('A1').setValue('1');
}

Time Trigger Location

Time Trigger Setting

Upvotes: 2

Related Questions