Variable Global Array with google sheets

user_15430725 0
Viewed 65

I'm newbie in Google Sheets/JavaScript. I have to port an VBA Excel application to google Sheets.

The Excel application contains a lot of formulas that use the values (> 2000 values) of on tab. In Excel, it imports all parameters at the beginning, and then it feeds to the formulas with an Global variable to avoid access to each formula call.

How can I read a big number array and store in one global variable in Google Sheets? I'm googling and it seems that I have to use PropertiesService, but it doesn't work for me.

My code:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var SS = spreadsheet.getSheetByName('Calibrated_Values')
var values  = SS.getRange("A:A").getValues();

var scriptProperties = PropertiesService.getScriptProperties();
scriptProperties.setProperty('setnumber', range)

var GetProp= scriptProperties.getProperty('setnumber');

for (var i=0;i<10;i++) {
    Logger.log('%d', GetProp));
}

Thanks

google-sheets global-variables excel javascript
1 Answers

On the right track. The issue is that according to this:

The Properties service stores all data as strings in key-value pairs. Data types that are not already strings are automatically converted to strings, including methods contained within saved objects.

If you try to save the result of getRange directly, you end up storing something like [Ljava.lang.Object;@3cdd0c95.

The simple solution here is we can use JSON.stringify to save the data:

function setNumber()
{
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let SS = spreadsheet.getSheetByName('Calibrated_Values');
  let values = SS.getRange("A:A").getValues();
  let scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('setnumber', JSON.stringify(values));
}

And to retrieve it, use JSON.parse:

function getNumber()
{
  let scriptProperties = PropertiesService.getScriptProperties();
  let prop = JSON.parse(scriptProperties.getProperty('setnumber'));
  // Prop is now an array:
  console.log(prop);
}
user_42943990
Related