Copy json string in gsheet exceeds runtime optimize code

Question

i'm new hear i hope i frase this correct.

I have a json string(around 10.000 lines and 7 columns), this string i read in via AppScript from GMail into a json object with AppScript and then i want to write it into different cells in a GoogleSheet. Unfortunatly my code takes too long and it stopps at line 3000.

Can you help me to optimize my code so it runs faster?

Unfortnatly i'm quite new to programming and my knowledge with JS and AppScript is nearly zero. So here is my code:

@JSON_START@ [{"A": "123123123123132","B": "String 1","C": "Int2","D": "String2","E": "YEAR-MN-DY","F": "YEAR-MT-DA","G": "String3"},{"A": "123123123123132","B": "String 1","C": "Int2","D": "String2","E": "YEAR-MN-DY","F": "YEAR-MT-DA","G": "String3"}]@JSON_END@ This is how my json String lookes like and then 10k more lines of this, the JSON_Start is to find the start of the json in the Mail.

And here my code which runs too slow (which is i understand just a real bad loop and so i hope there is a faster way):

var ss = SpreadsheetApp.openById(sheetId)
var row = 2;
var col = 1;

for (var key in json_obj){
    if (json_obj.hasOwnProperty(key)){
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].A);
      col = col + 1;
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].B);
      col = col + 1;
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].C);
      col = col + 1;
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].D);
      col = col + 1;
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].E);
      col = col + 1;
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].F);
      col = col + 1;
      ss.getSheetByName(rawdatasheetname).getRange(row, col).setValue(json_obj[key].G);
      col = 1;
      row = row + 1
    }
  }

I wasn't able to find a better way online and make it work. So this works but just as i said it is to slow.

Thank you

Jan

Also Related: https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept — TheMaster, Sep 24 '20 at 08:33
Related/Duplicate of https://stackoverflow.com/questions/35289183/ and https://stackoverflow.com/questions/7854573 Also read **Best practices** in documentation. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. — TheMaster, Sep 24 '20 at 11:03

score 1 · Accepted Answer · answered Sep 24 '20 at 10:15

Instead of iterating through all properties in the JSON object, you can first convert your object to a 2D array, and then use setValues(values):

var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(rawdatasheetname);
var row = 2;
var col = 1;
var array = json_obj.map(obj => Object.values(obj));
sheet.getRange(row, col, array.length, array[0].length).setValues(array);

This way, you shouldn't get anywhere near the execution time limit.

Note:

As you can see in the sample above, there's no need to repeat ss.getSheetByName(rawdatasheetname) every time. You can just declare a variable for the Sheet.

Reference:

Object.values()

Copy json string in gsheet exceeds runtime optimize code

1 Answers1

Note:

Reference: