🧑🏾 ⏬ 👩🏻‍🏭 Automation or Death: How to Manage Thousands of In-Game Content Using Google Sheets 💇🏽 👨‍👨‍👦 🎌

- : , . , , , — , . , , .

, -. , — , . , .

, , , , , , QA- .

- . , — , , , , .

Pixel Gun 3D : . ? Unity, . .

freemium. — , , . DAU , , , .

, 20-30 , , , . — , .

- .

, , . -.

- Unity

, Unity -. , . — .

Google Apps Script. , JSON. , JSON, .

, . #. .

function doGet(e)
{
 if (e === undefined || e.parameter === undefined)
 {
   return FailWithMessage("nullable parameters");
 }
 var tableId = e.parameter["table"];
 var listName = e.parameter["list"];
 if (listName !== undefined && listName !== "" && listName !== "null")
 {
   var startRow = parseInt(e.parameter["startRow"]);
   var startColumn = parseInt(e.parameter["startColumn"]);
   var numRow = parseInt(e.parameter["numRow"]);
   var numColumn = parseInt(e.parameter["numCol"]);
   return GetSigleList(tableId, listName, startRow, startColumn, numRow, numColumn);
 }
 else
 {
   return GetAllTable(tableId);
 }
}
 
 
function GetSigleList(tableId, listName, startRow, startColumn, numRow, numColumn)
{
 var ss = SpreadsheetApp.openById(tableId);
 if (ss == null)
 {
   return FailWithMessage("table with name: " + tableId + "not found");
 }
 var sheet = ss.getSheetByName(listName);
 if (sheet == null)
 {
   return FailWithMessage("list with name: " + listName + "not found");
 }
  if (numRow < 1) numRow = sheet.getLastRow();
 if (numColumn < 1) numColumn = sheet.getLastColumn();
 var range = sheet.getRange(startRow, startColumn, numRow, numColumn);
 var data = range.getValues();
 var str = JSON.stringify(data);
  var resultObject = {
   "resultCode": 2,
   "message": str
 };
 var result = JSON.stringify(resultObject);
 return ContentService.createTextOutput(result);
}
 
 
function GetAllTable(tableId)
{
 var ss = SpreadsheetApp.openById(tableId);
 if (ss == null)
 {
   return FailWithMessage("table with name: " + tableId + "not found");
 }
  var result = {};
  var listModes = ss.getSheets();
 for(var i = 0; i< listModes.length; i++)
 {
   var sheet = listModes[i];
   var sheetName = sheet.getSheetName();
  
   var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
   var data = range.getValues();
   result[sheetName] = data;
 }
  var str = JSON.stringify(result);
  var resultObject = {
   "resultCode": 2,
   "message": str
 };
  var result = JSON.stringify(resultObject);
 return ContentService.createTextOutput(result);
}
 
function FailWithMessage(message)
{
 var result = {
   "resultCode": 1,
   "message": message
 };
  
 var str = JSON.stringify(result); 
 return ContentService.createTextOutput(str);
}

, , , , .

https://script.google.com/macros/s/WwlCZODTDRXJaHhdjfwFRcKtHRQOHqzYisjndduZzDihMpXehLrNxdi/exec

. , , get- ID . URL . , https://docs.google.com/spreadsheets/d/example_habr/edit#gid=0, ID — example_habr.

, — . .

https://script.google.com/macros/s/WwlCZODTDRXJaHhdjfwFRcKtHRQOHqzYisjndduZzDihMpXehLrNxdi/exec?table=example_habr&list=MyList&startRow=1&startColumn=2&numRow=10&numCol=5

Google . , , — .

, . . , 5 : , «». — . , - .

, . , , , . — .

Unity -

, : ID , , . : , , . — , .

Unity, — ? .

. , 500 Google Play. , . , ? , - , . , — =( ). .

- . , , 27- . , , , , , .

- , Google.

, ( : , , ). , , , . , , , . , .

, — . , Weapon970 , . .

. (, ). , , . . — , .

. «» — , , , . , . 40 , , .

. ( - ), . .

, . , .

, . , , , .

, , , . , .

, , 50 — . , , , . , — , . — .

, . , 140 (, 10 30 ). — , . - — .

, . , .

=ifs(I2="EndMatch";ifs(AE2<=TasksData!$O$34+TasksData!$N$34;"TeamDuel";AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34;(A2=0;"TeamDuel";"Spleef");AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34;"Duel";AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34+TasksData!$K$34;(A2=0;"Duel";"BattleRoyale");AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34+TasksData!$K$34+TasksData!$J$34;(A2=0;"TeamFight";"DeadlyGames");AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34+TasksData!$K$34+TasksData!$J$34+TasksData!$I$34;"CapturePoints";AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34+TasksData!$K$34+TasksData!$J$34+TasksData!$I$34+TasksData!$H$34;"FlagCapture";AE2<=TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34+TasksData!$K$34+TasksData!$J$34+TasksData!$I$34+TasksData!$H$34+TasksData!$G$34;"Deathmatch";AE2>TasksData!$O$34+TasksData!$N$34+TasksData!$M$34+TasksData!$L$34+TasksData!$K$34+TasksData!$J$34+TasksData!$I$34+TasksData!$H$34+TasksData!$G$34;"TeamFight"); I2="killPlayer";ifs(AE2<=TasksData!$N$35;"TeamDuel";AE2<=TasksData!$N$35+TasksData!$L$35;"Duel";AE2<=TasksData!$N$35+TasksData!$L$35+TasksData!$K$35;(A2=0;"TeamFight";"BattleRoyale");AE2<=TasksData!$N$35+TasksData!$L$35+TasksData!$K$35+TasksData!$J$35;"DeadlyGames";AE2<=TasksData!$N$35+TasksData!$L$35+TasksData!$K$35+TasksData!$J$35+TasksData!$I$35;"CapturePoints";AE2<=TasksData!$N$35+TasksData!$L$35+TasksData!$K$35+TasksData!$J$35+TasksData!$I$35+TasksData!$H$35;"FlagCapture";AE2<=TasksData!$N$35+TasksData!$L$35+TasksData!$K$35+TasksData!$J$35+TasksData!$I$35+TasksData!$H$35+TasksData!$G$35;"Deathmatch";AE2>TasksData!$N$35+TasksData!$L$35+TasksData!$K$35+TasksData!$J$35+TasksData!$I$35+TasksData!$H$35+TasksData!$G$35;"TeamFight"); I2="killPet";ifs(AE2<=TasksData!$G$36;"Deathmatch";AE2>TasksData!$G$36;"TeamFight"); I2="killPlayerThroughWall";ifs(AE2<=TasksData!$I$37;"CapturePoints";AE2<=TasksData!$I$37+TasksData!$H$37;"FlagCapture";AE2<=TasksData!$I$37+TasksData!$H$37+TasksData!$G$37;"Deathmatch";AE2>TasksData!$I$37+TasksData!$H$37+TasksData!$G$37;"TeamFight"); I2="killPlayerFlying";ifs(AE2<=TasksData!$I$38;"CapturePoints";AE2<=TasksData!$I$38+TasksData!$H$38;"FlagCapture";AE2<=TasksData!$I$38+TasksData!$H$38+TasksData!$G$38;"Deathmatch";AE2>TasksData!$I$38+TasksData!$H$38+TasksData!$G$38;"TeamFight");I2="ramEscort";"Siege";I2="escortDestroyGate";"Siege";I2="winBrNoChest";"BattleRoyale";I2="crashChest";"BattleRoyale";I2="winBrInParty";"BattleRoyale";I2="flagCapture";"FlagCapture";I2="pointCapture";"CapturePoints")

( , Key_ — , ):

=IFS(I2="endMatch";((T2=0;"Key_7220";"Key_7234"));I2="killPet";(W2="None";"Key_7228";"Key_7224");I2="killPlayer";(Q2=1;"Key_7227";((W2="NONE";(R2=1;"Key_7232";(S2=1;"Key_7233";"Key_7221"));"Key_7216")));I2="killPlayerFlying";"Key_7225";I2="killPlayerThroughWall";"Key_7226";I2="ramEscort";"Key_7235";I2="escortDestroyGate";"Key_7236";I2="winBrNoChest";"Key_7229";I2="crashChest";"Key_7230";I2="winBrInParty";"Key_7231";I2="flagCapture";"Key_7237";I2="pointCapture";"Key_7238";I2="";"")

, :

=ifs(L3="DeadlyGames";0;L3="BattleRoyale";0;L3="TeamDuel";0;1=1;(I3="killPlayer";ifs(A3=0;(($I$2:I2;"killPlayer")>=(TasksData!$B$34+TasksData!$B$36+TasksData!$B$38)*6;(($I$2:I2;"killPlayer")<(TasksData!$B$34+TasksData!$B$36+TasksData!$B$38+TasksData!$B$47)*6;1;0);0);A3=1;(($I$2:I2;"killPlayer")>=(TasksData!$B$34+TasksData!$B$36+TasksData!$B$38+TasksData!$B$47+TasksData!$B$48+TasksData!$C$34+TasksData!$C$36+TasksData!$C$38)*6;(($I$2:I2;"killPlayer")<(TasksData!$B$34+TasksData!$B$36+TasksData!$B$38+TasksData!$C$34+TasksData!$C$36+TasksData!$C$38+TasksData!$B$47+TasksData!$B$48+TasksData!$C$47)*6;1;0);0);A3=2;(($I$2:I2;"killPlayer")>=(TasksData!$B$34+TasksData!$B$36+TasksData!$B$38+TasksData!$B$47+TasksData!$B$48+TasksData!$C$34+TasksData!$C$36+TasksData!$C$38+TasksData!$C$47+TasksData!$C$48+TasksData!$D$34+TasksData!$D$36+TasksData!$D$38)*6;(($I$2:I2;"killPlayer")<(TasksData!$B$34+TasksData!$B$36+TasksData!$B$38+TasksData!$B$48+TasksData!$C$34+TasksData!$C$36+TasksData!$C$38+TasksData!$D$34+TasksData!$D$36+TasksData!$D$38+TasksData!$B$47+TasksData!$C$47+TasksData!$C$48+TasksData!$D$47)*6;1;0);0));0))

. : . , . QA-, , . - : , , 1000 . , — .

=(1;100)

=(13;15)*6

=(B4:F4)

=IFS($A32<=G32;"Mythic";$A32<=F32;"Legend";$A32<=E32;"Epic";$A32<=D32;"Rare";$A32<=C32;"Common")

, — .

, , . , , . — .

, , . , , . .

( ):

. , ( ), — ( ).

2. -, . CSV-.

3. . ( ):

http://_/// .jpg

=IMAGE("https://files.fm/u/wdrhemgnk#/view/special_offer_pixelman_reward_big.png")

=(((A4;importrange(“_”;“‘1’!B:F”);5;))=;(A4;importrange(“_”;“‘1’!B:F”);5;); (((A4;importrange(“_”;“‘1’!C:F”);4;))=;(A4;importrange(“_”;“‘1’!C:F”);4;); (((A4;importrange(“_”;“‘1’!D:F”);3;))=;(A4;importrange(“_”;“‘1’!D:F”);3;); (((A4;importrange(“_”;“‘1’!E:F”);2;))=;(A4;importrange(“_”;“‘1’!E:F”);2;);“ “))))

, . 30 , , 700 — .