示例#1
0
        public object Edit(SheetRow _Row)
        {
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = WebApiApplication.credential,
                ApplicationName       = WebApiApplication.ApplicationName
            });
            ValueRange v = new ValueRange();

            v.Range = "Data!A" + _Row.RowIndex;
            IList <IList <object> > ret = new List <IList <object> >();
            IList <object>          row = new List <object>();
            IList <ValueRange>      lst = new List <ValueRange>();

            foreach (string s in _Row.Row)
            {
                row.Add(s);
            }
            ret.Add(row);
            v.Values = ret;
            lst.Add(v);
            Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
            _body.Data             = lst;
            _body.ValueInputOption = "RAW";
            v.MajorDimension       = "ROWS";
            SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
                service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
            request2.Fields = "totalUpdatedColumns,totalUpdatedRows";
            BatchUpdateValuesResponse response2 = request2.Execute();

            return(response2.TotalUpdatedRows);
        }
示例#2
0
        public IActionResult SpreadEdit(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sa_spread_edit")] HttpRequest req, ILogger log)
        {
            _sheetsService = ConnectSpreadSheet();
            String range                      = "シート1!B2";
            string valueInputOption           = "USER_ENTERED";
            List <Data.ValueRange> updateData = new List <Data.ValueRange>();
            var dataValueRange                = new Data.ValueRange();

            List <IList <object> > data = new List <IList <object> >();

            data.Add(GetRandomList());
            data.Add(GetRandomList());
            data.Add(GetRandomList());
            data.Add(GetRandomList());
            data.Add(GetRandomList());
            dataValueRange.Range  = range;
            dataValueRange.Values = data;
            updateData.Add(dataValueRange);

            Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
            requestBody.ValueInputOption = valueInputOption;
            requestBody.Data             = updateData;
            var request = _sheetsService.Spreadsheets.Values.BatchUpdate(requestBody, _editSpreadsheetId);

            Data.BatchUpdateValuesResponse response = request.Execute();

            return(new ObjectResult(JsonConvert.SerializeObject(response)));
        }
示例#3
0
        private static void test()
        {
            string[] Scopes          = { SheetsService.Scope.Spreadsheets };
            string   ApplicationName = "Brawlhalla Stats";

            UserCredential credential;

            using (var stream =
                       new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
            {
                // The file token.json stores the user's access and refresh tokens, and is created
                // automatically when the authorization flow completes for the first time.
                string credPath = "token.json";
                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName       = ApplicationName,
            });

            // Define request parameters.
            String spreadsheetId = "1MwKJaBCy4xRbT0hKpKGOr6vfZHmzhy4uJ76nfD78YWA";
            String range         = "Ark1!A2:E";

            SpreadsheetsResource.ValuesResource.GetRequest request =
                service.Spreadsheets.Values.Get(spreadsheetId, range);


            range = "Ark1!A2:Y";
            string valueInputOption     = "USER_ENTERED";
            List <IList <object> > data = new List <IList <object> >();

            // The new values to apply to the spreadsheet.
            List <Data.ValueRange> updateData = new List <Data.ValueRange>();
            var dataValueRange = new Data.ValueRange();

            dataValueRange.Range  = range;
            dataValueRange.Values = data;
            updateData.Add(dataValueRange);

            Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
            requestBody.ValueInputOption = valueInputOption;
            requestBody.Data             = updateData;

            //request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);

            service.Spreadsheets.Values.Append(null, spreadsheetId, range);

            Console.Read();
        }
        /// Inserts the data into specific sheet name at row # provided
        private static void InsertData(SheetsService service, string sheetName, int row, List <IList <object> > data)
        {
            String range            = sheetName + "!A" + row + ":Y";
            string valueInputOption = "USER_ENTERED";

            // The new values to apply to the spreadsheet.
            List <Google.Apis.Sheets.v4.Data.ValueRange> updateData = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
            var dataValueRange = new Google.Apis.Sheets.v4.Data.ValueRange();

            dataValueRange.Range  = range;
            dataValueRange.Values = data;
            updateData.Add(dataValueRange);

            Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
            requestBody.ValueInputOption = valueInputOption;
            requestBody.Data             = updateData;

            var request = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);

            Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse response = request.Execute();
        }
示例#5
0
        public object Create(SheetRow _Row)
        {
            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = WebApiApplication.credential,
                ApplicationName       = WebApiApplication.ApplicationName
            });

            // Define request parameters.
            SpreadsheetsResource.ValuesResource.GetRequest request =
                service.Spreadsheets.Values.Get(spreadsheetId, "Data!A:B");
            ValueRange response            = request.Execute();
            IList <IList <Object> > values = response.Values;
            ValueRange v = new ValueRange();

            v.Range = "Data!A" + (values.Count + 1) + ":Z" + (values.Count + 1);
            IList <IList <object> > ret = new List <IList <object> >();
            IList <object>          row = new List <object>();
            IList <ValueRange>      lst = new List <ValueRange>();

            foreach (string s in _Row.Row)
            {
                row.Add(s);
            }
            ret.Add(row);
            v.Values = ret;
            lst.Add(v);
            Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
            _body.Data             = lst;
            _body.ValueInputOption = "RAW";
            v.MajorDimension       = "ROWS";
            SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
                service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
            request2.Fields = "totalUpdatedRows";
            BatchUpdateValuesResponse response2 = request2.Execute();

            return(response2.TotalUpdatedRows);
        }
 public static void InserViewCountToPopularityColumn(List <SpotifyInfo> songs, string columnName)
 {
     try
     {
         var service = UserCredential();
         IList <SpotifyInfo> dataList = songs;
         List <Google.Apis.Sheets.v4.Data.ValueRange> data = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
         ValueRange valueDataRange = new ValueRange()
         {
             MajorDimension = "ROWS"
         };
         valueDataRange.Values = new List <IList <object> >()
         {
         };
         valueDataRange.Values.Add(new List <object> {
             DateTime.Now.ToString("POPULARITY")
         });
         int max = dataList.Count + 1;
         valueDataRange.Range = range + "!" + columnName + "1:" + columnName + max.ToString();
         for (int i = 0; i < dataList.Count; i++)
         {
             IList <object> list = new List <object> {
                 dataList[i].Popularity
             };
             valueDataRange.Values.Add(list);
         }
         data.Add(valueDataRange);
         Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
         requestBody.ValueInputOption = "USER_ENTERED";
         requestBody.Data             = data;
         // API to update data to sheet
         SpreadsheetsResource.ValuesResource.BatchUpdateRequest request  = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
         Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse   response = request.Execute();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
 }
示例#7
0
        private void writeToGoogleSheet(DataTable dt)
        {
            List <IList <Object> > googleDataRows = new List <IList <Object> >();
            var googleDataRow = new List <object>();

            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < 6; i++)
                {
                    googleDataRow.Add(row[i]);
                }

                googleDataRows.Add(googleDataRow);
                googleDataRow = new List <object>();
            }

            // The new values to apply to the spreadsheet.
            List <Data.ValueRange> data = new List <Data.ValueRange>();  // TODO: Update placeholder value.

            Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
            requestBody.ValueInputOption = "RAW";

            ValueRange vr = new ValueRange();

            vr.Values = googleDataRows;
            vr.Range  = monthStrings[monthIndex];

            data.Add(vr);

            requestBody.Data = data;

            SpreadsheetsResource.ValuesResource.BatchUpdateRequest request = Program.SpreadsheetService.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);

            // To execute asynchronously in an async method, replace `request.Execute()` as shown:
            Data.BatchUpdateValuesResponse response = request.Execute();

            Console.Read();
        }
 public static void InsertLinkYoutube(List <Songs> songs, int numberOfOldSongs)
 {
     try
     {
         var           service  = UserCredential();
         IList <Songs> dataList = songs;
         List <Google.Apis.Sheets.v4.Data.ValueRange> data = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
         ValueRange valueDataRange = new ValueRange()
         {
             MajorDimension = "ROWS"
         };
         valueDataRange.Values = new List <IList <object> >()
         {
         };
         int max = numberOfOldSongs + songs.Count + 1;
         valueDataRange.Range = range + "!K" + (numberOfOldSongs + 2).ToString() + ":" + "K" + max.ToString();
         for (int i = 0; i < dataList.Count; i++)
         {
             IList <object> list = new List <object> {
                 dataList[i].YoutubeUrl
             };
             valueDataRange.Values.Add(list);
         }
         data.Add(valueDataRange);
         Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
         requestBody.ValueInputOption = "USER_ENTERED";
         requestBody.Data             = data;
         // API to update data to sheet
         SpreadsheetsResource.ValuesResource.BatchUpdateRequest request  = service.Spreadsheets.Values.BatchUpdate(requestBody, spreadsheetId);
         Google.Apis.Sheets.v4.Data.BatchUpdateValuesResponse   response = request.Execute();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
 }