[INTERFACE]
DIALOG, "Заполнение Гугл Таблицы Python", 792, 388;
STATIC, "Путь к файлу ключа JSON", 4, 4, 136, 20;
EDIT, 144, 4, 532, 20, string JSON_KEY;
BUTTON_FILE, "Выбрать файл", 680, 4, 100, 20, JSON_KEY;
EDIT, 296, 28, 380, 20, string ID_LINK_PATH;
BUTTON_FILE, "Выбрать файл", 680, 28, 100, 20, ID_LINK_PATH;
COMBO, "Открыть идентификатор таблицы с файла""Указать идентификатор таблицы", 4, 40, 288, 104, int G_ID_LINK_PATH;
EDIT, 296, 52, 380, 20, string ID_CREATEGS;
CHECK, "Установить ширину столбцов (0-3=300 означает ширина A, B и C = 300 пикселей:", 4, 76, 436, 20, int GWidth;
EDIT_MULTILINE, 456, 76, 156, 96, string Width;
CHECK, "Установить высоту рядов (1-5=50 означает высота радов 2, 3, 4, 5 = 50 пикселей:", 4, 180, 448, 20, int GHeight;
EDIT_MULTILINE, 456, 180, 156, 96, string Height;
CHECK, "Обнулить массив данных при успешном обновление", 4, 280, 296, 20, int G_Reset;
STATIC, "Комментарий", 4, 304, 76, 20;
EDIT, 84, 304, 696, 20, unknown Comment;
BUTTON_OK, "Принять", 572, 332, 100, 20;
BUTTON_CANCEL, "Закрыть", 680, 332, 100, 20;
[END]

[APPEARANCE]
COLOR =120, 50, 140;
SIZE_X = 14;
SIZE_Y =4;
[END]

[INFO]
[ENDL]>>Comment
[END]

[ASSOC_VAR]
JSON_KEY=pathfolder()+"python\my-project-121364-fh53hjkk7ghj.json";
G_ID_LINK_PATH=0;
ID_LINK_PATH=pathfolder()+"LinkGoogleSheets.txt";
ID_CREATEGS="15gWihFetTlSPsih4Kbw-BEte-maX-Qd_NC-wUUHtRMI";
GWidth=0;
Width="0-3=300";
GHeight=0;
Height="0-1=70";
G_Reset=1;
Comment=;
[END]

[GLOBAL_VAR]
int Import_CreateGoogleSheets=0;  
string PathKey_CreateGoogleSheets="";
int KML_GoogleSheets=0;
int Size_FillGoogleSheets=-1;
[END]

[EVENT]
IF (GWidth==1)ENABLE(GETID(Width), 1);
IF (GWidth==0)ENABLE(GETID(Width), 0);
IF (GHeight==1)ENABLE(GETID(Height), 1);
IF (GHeight==0)ENABLE(GETID(Height), 0);
IF (G_ID_LINK_PATH==0)ENABLE(GETID(ID_LINK_PATH), 1);
IF (G_ID_LINK_PATH==0)ENABLE(GETID(ID_CREATEGS), 0);
IF (G_ID_LINK_PATH==1)ENABLE(GETID(ID_LINK_PATH), 0);
IF (G_ID_LINK_PATH==1)ENABLE(GETID(ID_CREATEGS), 1);
[END]

[FUNCTION_BODY]
function(string pathkey, int g_pathfile, string pathfile, string creategs, int gwidth, string width, int gheight, string height, int reset)
{
  if (Import_CreateGoogleSheets==0)  
  {  
  Import_CreateGoogleSheets=1; 
  __python_sync
#Импорт
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials
__end_python    
  }
sendgetpython("CREDENTIALS_FILE = r^'"+pathkey+"^'");
  if (PathKey_CreateGoogleSheets!=pathkey)
  {
  PathKey_CreateGoogleSheets=pathkey;
  __python_sync
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive'])
httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http = httpAuth)
__end_python  
  }
  if (g_pathfile==0)
  {
    if (fopen (pathfile, "r")!=0)
    {
    freadline(creategs);//Считываем spreadsheetId таблицы
    fclose();
    }
  }
sendgetpython("spreadsheetId = ^'"+creategs+"^'");
__python_sync
spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute()
sheetList = spreadsheet.get('sheets')
for sheet in sheetList:
__end_python
string sheetname=sendgetpython("    print(sheet[^'properties^'][^'title^'])^n");
string P[3];
int tp, n;
  if (gwidth)
  {
  width=width+"^r^n";
  tp=0;
    if (regexsearch(3, #P[0], width, "[0-9]+(?=-|=|\r\n)")==3)
    {
    sendgetpython("width = ["+P[0]+", "+P[1]+", "+P[2]+"]");
    tp=tp+3;
      while (regexsearch(3, #P[0])==3)
      {
      sendgetpython("width.append("+P[0]+")");
      sendgetpython("width.append("+P[1]+")");
      sendgetpython("width.append("+P[2]+")");
      tp=tp+3;
      }
    }
  __python_sync
results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = {
__end_python
  sendgetpython("^"requests^": [");
    for (n=0; n<tp; n=n+3)
    {
    __python_sync
    # Задать ширину столбца
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",  # COLUMNS - потому что столбец
__end_python
    sendgetpython("          ^"startIndex^": width["+format(n)+"],");
    sendgetpython("          ^"endIndex^": width["+format(n+1)+"]");
    __python_sync
        },
        "properties": {
__end_python
    sendgetpython("          ^"pixelSize^": width["+format(n+2)+"]");
    __python_sync
        },
        "fields": "pixelSize"  # нужно задать только pixelSize и не трогать другие параметры столбца
      }
    }
__end_python
    if (n+3<tp)sendgetpython(",");
      else
      {
      __python_sync
  ]
}).execute()
__end_python
      }
    }    
  }  
  if (gheight)
  {
  height=height+"^r^n";
  tp=0;
    if (regexsearch(3, #P[0], height, "[0-9]+(?=-|=|\r\n)")==3)
    {
    sendgetpython("height = ["+P[0]+", "+P[1]+", "+P[2]+"]");
    tp=tp+3;
      while (regexsearch(3, #P[0])==3)
      {
      sendgetpython("height.append("+P[0]+")");
      sendgetpython("height.append("+P[1]+")");
      sendgetpython("height.append("+P[2]+")");
      tp=tp+3;
      }
    }
  __python_sync
spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute()
results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = {
__end_python
  sendgetpython("^"requests^": [");
    for (n=0; n<tp; n=n+3)
    {
    __python_sync
    # Задать высоту ряда
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "ROWS",  # ROWS - потому что ряд
__end_python
    sendgetpython("          ^"startIndex^": height["+format(n)+"],");
    sendgetpython("          ^"endIndex^": height["+format(n+1)+"]");
    __python_sync
        },
        "properties": {
__end_python
    sendgetpython("          ^"pixelSize^": height["+format(n+2)+"]");
    __python_sync
        },
        "fields": "pixelSize"  # нужно задать только pixelSize и не трогать другие параметры ряда
      }
    }
__end_python
    if (n+3<tp)sendgetpython(",");
      else
      {
      __python_sync
  ]
}).execute()
__end_python
      }
    }    
  }  
  if (Size_FillGoogleSheets==-1)
  {
  Size_FillGoogleSheets=1000;
  global new int CN_GoogleSheets[2][Size_FillGoogleSheets];
  global new int FI_GoogleSheets[Size_FillGoogleSheets];
  global new string Values_GoogleSheets[Size_FillGoogleSheets];
  }
string cs, t="";
if (KML_GoogleSheets==0)return 1;
sendgetpython("results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheet[^'spreadsheetId^'], body = {^"valueInputOption^": ^"USER_ENTERED^",^"data^": [");
  for (n=0; n<KML_GoogleSheets; n++)
  {
  if (CN_GoogleSheets[0][n]<26) cs=formatic(CN_GoogleSheets[0][n]+65)+format(CN_GoogleSheets[1][n]);
  else cs="A"+formatic(CN_GoogleSheets[0][n]+65-26)+format(CN_GoogleSheets[1][n]);
    if (FI_GoogleSheets[n]==0)
    {
    if (n+1<KML_GoogleSheets)sendgetpython("{^"range^": ^""+sheetname+"!"+cs+"^","+"^"majorDimension^": ^"ROWS^","+"^"values^": [[^""+Values_GoogleSheets[n]+"^"]]},");
    else t=sendgetpython("{^"range^": ^""+sheetname+"!"+cs+"^","+"^"majorDimension^": ^"ROWS^","+"^"values^": [[^""+Values_GoogleSheets[n]+"^"]]}]}).execute()");
    }
    else
    {
    if (n+1<KML_GoogleSheets)sendgetpython("{^"range^": ^""+sheetname+"!"+cs+"^","+"^"majorDimension^": ^"ROWS^","+"^"values^": [[^""+Values_GoogleSheets[n]+"^"]]},");
    else t=sendgetpython("{^"range^": ^""+sheetname+"!"+cs+"^","+"^"majorDimension^": ^"ROWS^","+"^"values^": [["+Values_GoogleSheets[n]+"]]}]}).execute()");
    }
  }
t=t+sendgetpython("print(^"1^")");
  if (regexsearch(1, #t, t, "ERROR_READ_PYTHON1")==1)
  {
  if (reset)KML_GoogleSheets=0;
  return 1;
  }
return 0;
}
[END]

[CALL]
(JSON_KEY, G_ID_LINK_PATH, ID_LINK_PATH, ID_CREATEGS, GWidth, Width, GHeight, Height, G_Reset)
[END]

[FUNCTION_LOGIC]  
==1
[END]
