I am working on a Google sheet script to manage stocks of items in a game, which is supposed to work as such:

People can make request to deposite or withdraw items using a Google form, which send all the infos, including what resource and in what amount, to a first "log" sheet. I then want a script to read these logs, and use them to update a different sheet, which show the actual stocks.

I should mention, there's about 800 different items to stock, and we like to move them around (up or down the list) because we're dumb.

So my idea what the have the script first retrieve the name of the item we made a request for, then try to match it in the stock sheet.

If it can, it should then add or substract the amount to the stock.

If it can't, it should just colour the log line in red so we can see it and redo the request.

My first problem is that I have no idea if a script in Gsheet can stay active for a long time, and the second is that I have even less of an idea how to properly retrieve a string of text and store it, then compare it with others, and that 800 times each time.

Thank you !

CodePudding user response：

From the question

My first problem is that I have no idea if a script in Gsheet can stay active for a long time,

Google Apps Script have quotas. In this case, the corresponding quota is the execution time limit. For free accounts the limit is 6 minutes, for Workspace accounts the limit is 30 minutes.

and the second is that I have even less of an idea how to properly retrieve a string of text and store it, then compare it with others, and that 800 times each time.

Start by reading

And your data sheet looks like this:

Here is the function that takes all items from the log sheet, sums them and put on the data sheet:

function add_all_items_from_log() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var log = ss.getSheetByName('log').getDataRange().getValues();

  // put all data into the object {item1:q, item2:q, item3:q, ...etc}
  var obj = {};
  for (let [date, item, q] of log) {
    if (item in obj) obj[item]  = q; else obj[item] = q;
  }
  console.log(obj);

  // convert the object into a 2d array [[item1,q], [item2,q], [item3,q], ...]
  var array = Object.keys(obj).map(key => [key, obj[key]]);
  console.log(array);
  
  // put the array on the data sheet (starting from second row)
  var sheet = ss.getSheetByName('data');
  sheet.getRange(2,1,sheet.getLastRow()).clearContent();
  sheet.getRange(2,1,array.length, array[0].length).setValues(array);
}

The result:

Here is the function that takes item from the last line of the log sheet and add the item to the data sheet:

function add_last_item_from_log() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get item from the last row of the log sheet
  var [date, item, q] = ss.getSheetByName('log').getDataRange().getValues().pop();
  console.log(date, item, q);

  // get data from the data sheet
  var sheet = ss.getSheetByName('data');
  var [header, ...data] = sheet.getDataRange().getValues();
  
  // put the data into the object {item1:q, item2:q, item3:q, ...etc}
  var obj = {};
  data.forEach(row => obj[row[0]] = row[1]);
  console.log(obj);

  // add the item to the object
  if (item in obj) obj[item]  = q; else obj[item] = q;
  
  // convert the object into a 2d array [[item1,q], [item2,q], [item3,q], ...]
  var array = Object.keys(obj).map(key => [key, obj[key]]);
  console.log(array);

  // put the array on the sheet (starting from second row)
  var sheet = ss.getSheetByName('data');
  sheet.getRange(2,1,sheet.getLastRow()).clearContent();
  sheet.getRange(2,1,array.length, array[0].length).setValues(array);
}

Here is my sheet.

You can run these function manually from Text Editor. Just to see how it works. But actually, as far as I can tell, you better to run the last function (or its variant) automatically every time the log sheet is updated from the Form submit. It can be done with the trigger onFormSubmit().

And this is a simplest case. If you have 800 items and many columns the code may require some optimizations.