public async Task <string> AddThumbnails() { Response.Headers.Add("Cache-Control", "no-cache"); string result = string.Empty; string newFolderId = null; string imagesFolderId = null; string accountName = this._httpContextAccessor.HttpContext.Request.Headers[DriveImportConstants.VTEX_ACCOUNT_HEADER_NAME]; FolderIds folderIds = await _driveImportRepository.LoadFolderIds(accountName); if (folderIds != null) { newFolderId = folderIds.NewFolderId; imagesFolderId = folderIds.ImagesFolderId; ListFilesResponse imageFiles = new ListFilesResponse(); imageFiles.Files = new List <GoogleFile>(); string nextPageToken = string.Empty; do { ListFilesResponse listFilesResponse = await _googleDriveService.ListImagesInFolder(newFolderId, nextPageToken); imageFiles.Files.AddRange(listFilesResponse.Files); nextPageToken = listFilesResponse.NextPageToken; } while (!string.IsNullOrEmpty(nextPageToken)); ListFilesResponse spreadsheets = await _googleDriveService.ListSheetsInFolder(imagesFolderId); if (imageFiles != null && spreadsheets != null) { var sheetIds = spreadsheets.Files.Select(s => s.Id); if (sheetIds != null) { foreach (var sheetId in sheetIds) { Dictionary <string, int> headerIndexDictionary = new Dictionary <string, int>(); string sheetContent = await _googleDriveService.GetSheet(sheetId, string.Empty); GoogleSheet googleSheet = JsonConvert.DeserializeObject <GoogleSheet>(sheetContent); string valueRange = googleSheet.ValueRanges[0].Range; string sheetName = valueRange.Split("!")[0]; string[] sheetHeader = googleSheet.ValueRanges[0].Values[0]; int headerIndex = 0; int rowCount = googleSheet.ValueRanges[0].Values.Count(); int writeBlockSize = rowCount; foreach (string header in sheetHeader) { headerIndexDictionary.Add(header.ToLower(), headerIndex); headerIndex++; } string[][] arrValuesToWrite = new string[writeBlockSize][]; for (int index = 1; index < rowCount; index++) { string imageFileName = string.Empty; string[] dataValues = googleSheet.ValueRanges[0].Values[index]; if (headerIndexDictionary.ContainsKey("image") && headerIndexDictionary["image"] < dataValues.Count()) { imageFileName = dataValues[headerIndexDictionary["image"]]; } GoogleFile file = imageFiles.Files.FirstOrDefault(f => f.Name.Equals(imageFileName)); if (file != null) { string[] row = new string[headerIndexDictionary.Count]; row[headerIndexDictionary["thumbnail"]] = $"=IMAGE(\"{ file.ThumbnailLink}\")"; arrValuesToWrite[index - 1] = row; } } string lastColumn = ((char)(headerIndexDictionary.Count + 65)).ToString(); ValueRange valueRangeToWrite = new ValueRange { Range = $"{sheetName}!A2:{lastColumn}{rowCount + 1}", Values = arrValuesToWrite }; await _googleDriveService.WriteSpreadsheetValues(sheetId, valueRangeToWrite); } } } } return(result); }