GAS: How to create a globally accessible Array variable across multiple functions

Question

So, I have been asked to rephrase my question post. I actually thought it would make sense if I shared a picture of what I need to accomplish. My apologies for not knowing the format. But anywho ...

Required: I have a spreadsheet file and a user enters a value in a specific cell (see picture below). I want that value be stored along with the current date in a two dimensional array.

Screen Shot

What my code does: This code is (1) Using a PropertiesService to store a value globally (2) Accepting user input in H3 cell and is updating J3 cell with a date of format mm/dd and appending the user input. For e.g., if user inputs 4 in H3 and current date is 8/29/2019, then J3 will display "8/29::4" (3) This code also adds a comment with the above value in H3

Error/Issue (1) When I try to declare an array in the onEdit function and attempt to write something into it, it is spitting this error:

TypeError: Cannot read property 'push' of undefined at onEdit(Code:21:29)

var scriptProperties = PropertiesService.getScriptProperties();
scriptProperties.setProperty('i', 0);


function onEdit(e){
  if (e.range.getA1Notation() == "H3") 
  {  
    var difference = isNaN(e.value) ? 0 : Number(e.value);  
    var date = (new Date()); 
    var monthValue = date.getMonth();    
    var dateValue = date.getDate();
    var wholeValues = [];

    monthValue = monthValue+1;
    dateValue = dateValue-1;
    var logTimestamp = monthValue+"/"+dateValue;
    e.range.getSheet().getRange("J3").setValue(logTimestamp+"::"+difference+";"); 
    e.range.setNote(logTimestamp+"::"+difference+";");

    var arrayIndex = Number(scriptProperties.getProperty('i')); 
    wholeValues[arrayIndex].push("Sample Data");
    arrayIndex = arrayIndex + 1;
    Logger.log("wholeValues size is " +wholeValues.length);
   }
  }

Welcome to Stack Overflow. Please see How to Ask a Question and edit your post--your post should use images to help illustrate text, not to remove the question from your post, and you should clearly state what the problem you are experiencing is. Providing an example spreadsheet may also help. — sinaraheneba
– sinaraheneba, Commented Aug 29, 2019 at 6:46
As to your issue, the way I would do it is to store your other values on an extra sheet (e.g., cells in the top row, dates on the A column, values on each date in each cell's associated column on each associated date). It is then fairly trivial to get/write each value in each cell for the correct date, and retrieve the values later. see also the onEdit(e) trigger — sinaraheneba
– sinaraheneba, Commented Aug 29, 2019 at 6:49
What is your problem with the current code? See minimal reproducible example. You should provide a clear problem statement/error if any. — TheMaster
– TheMaster, Commented Aug 29, 2019 at 7:59
@TheMaster: I have rephrased my question. I hope it is in line with the question format. Could you please help — Jrules80
– Jrules80, Commented Aug 29, 2019 at 15:48
@sinaraheneba: Storing values in different places of the spreadsheet is not efficient as per my requirements. I need an array to accomplish this and having an array would let me search the items at a later time. — Jrules80
– Jrules80, Commented Aug 29, 2019 at 15:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Issues:

wholeValues array is local and won't persist across sessions/function calls/script runs.
wholeValues is a empty array all the time.
i will always be "0" as you're setting it to 0 in global scope each time a function is called.
wholeValues[0](=[][0]) is undefined as there is no element with index 0. undefined doesn't have .push function. Therefore, Cannot read property 'push' of undefined

Solution:

Different approaches can be made:

Create a object dateObj with value {date1:value1,date2:value2,...} and store it in properties as a string.
Set the datestring itself as a key to properties. Here each property is object containing {date:value}
You can save the array in a log sheet.

Snippet:

/*var scriptProperties = PropertiesService.getScriptProperties();
scriptProperties.setProperty('i', 0);*/ //Removed from global scope


function onEdit(e){
  if (e.range.getA1Notation() == "H3") 
  {  
   /* Rest of your code*/
    e.range.setNote(logTimestamp+"::"+difference+";");

    var scriptProperties = PropertiesService.getScriptProperties();
    var dateObjStr = scriptProperties.getProperty('dateObj'); 
    var dateObj = dateObjStr ? JSON.parse(dateObjStr) : {};
    var dateStr = Utilities.formatDate(date, e.source.getSpreadsheetTimeZone(), "YYYY-MM-dd");
    dateObj[dateStr] = difference;//{"2019-08-28": 6}
    scriptProperties.setProperty('dateObj', JSON.stringify(dateObj));
  }
}

Snippet#2:

    var scriptProperties = PropertiesService.getScriptProperties();
    var dateStr = Utilities.formatDate(date, e.source.getSpreadsheetTimeZone(), "YYYY-MM-dd");
    scriptProperties.setProperty(dateStr, difference);
    var dateVal = scriptProperties.getProperty(dateStr) || 0; //Retrieval

Snippet#3:

    var dateStr = Utilities.formatDate(date, e.source.getSpreadsheetTimeZone(), "YYYY-MM-dd");
    e.source.getSheetByName('LogSheet').appendRow([dateStr, difference]);

References:

Quotas

Properties value size 9kB / val
Properties total storage 500kB / property store
Properties read/write 50,000 - 500,000/ day
PropertiesService
Utilities#formatDate

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 29, 2019 at 16:25

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jrules80 Over a year ago

Thank you, kind sir. I'll check and report soon.

Jrules80 Over a year ago

A few questions: 1. Are Snipper #2 and #3 part of the onEdit function? 2. How long does the variable dateStr persist? 3. Why can't I use an Array? I'd really appreciate if you'd give me an example of how to achieve this with a persistable array.

TheMaster Over a year ago

@Jrules 1. Yes(alternative ways to snippet#1) 2. It doesn't. It dies immediately after function ends. Whatever is put in Properties persist. 3. Given your requirement, objects are the best choice.

Jrules80 Over a year ago

If I could persist objects indefinitely, can I use them like arrays? The reason why I ask is, as part of my requirement, I need to be able to go back and update the hours (difference variable) for a given timestamp. How do I do this with an object?

Jrules80 Over a year ago

So, this is what I did: scriptProperties.setProperty(dateStr, difference); var data = scriptProperties.getProperties(); // var dateVal = scriptProperties.getProperty(value) || 0; //Retrieval for (var key in data) { Logger.log('Key: %s, Value: %s', key, data[key]); } It is now able to retrieve "Date" and "User Input". But, the next time, user enters a value, it is being overwritten. I do not want that. I want to persist old "Date" and "User Input" values :(

|

Collectives™ on Stack Overflow

GAS: How to create a globally accessible Array variable across multiple functions

1 Answer 1

Issues:

Solution:

Snippet:

Snippet#2:

Snippet#3:

References:

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Issues:

Solution:

Snippet:

Snippet#2:

Snippet#3:

References:

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related