Google Sheet API v4 insert new row

Question

I use Google Sheet API v4 and I would like to insert new row in my sheet. I tried https://developers.google.com/sheets/api/guides/values#appending_values but that way just insert the data and I want to add the data to specific column like here: Is there a way to add a row on a Google Spreadsheet without OAuth using java? this is an example how to do that using api v3

    row.getCustomElements().setValueLocal("id", "21");
    row.getCustomElements().setValueLocal("type", si.getType().toString());
    row.getCustomElements().setValueLocal("longitude", String.valueOf(si.getLongitude()));
    row.getCustomElements().setValueLocal("latitude", String.valueOf(si.getLatitude()));
    row.getCustomElements().setValueLocal("last_maint", String.valueOf(si.getLast()));
    row.getCustomElements().setValueLocal("inst_date", String.valueOf(si.getInst()));

    row = service.insert(listFeedUrl, row);

How can I do the same using api v4.

score 5 · Answer 1 · edited Apr 09 '20 at 10:05

Follow this Quick Start to setup your android project to use Google Spreadsheet API v4 https://developers.google.com/sheets/api/quickstart/android

After the setup you read to use the API.

Code to insert a new row

In the MainActivity, in private class MakeRequestTask extends AsyncTask<Void, Void, List<String>> add insertDataToApi() funtion

        private boolean insertDataToApi() throws IOException{
        String spreadsheetId = "your_spreadsheet_id";
        String range = "MySheet!A1:C"; // A1:C refers to the 1st column of "MySheet", similarlly B1:B 2nd column. For more understanding https://developers.google.com/sheets/api/guides/concepts

        List<Object> row1 = new ArrayList<>();
        row1.add("Name");
        row1.add("Rollno");
        row1.add("Class");
        // similarly create more rows with data

        List<List<Object>> vaules = new ArrayList<>();
        vaules.add(row1);

        ValueRange valueRange = new ValueRange();
        valueRange.setMajorDimension("ROWS");
        valueRange.setValues(vaules);

        UpdateValuesResponse response = this.mService.spreadsheets().values()
                .update(spreadsheetId, range, valueRange)
                .setValueInputOption("RAW")
                .execute();

        Log.e("Update_response", response.toString());

        return true;
    }

Now just call insertDataToApi() in the protected List<String> doInBackground(Void... params){}

How to display datepicker by double click on specific column using rest API? — Parthiv, Jul 05 '18 at 11:17
These are the support NumberFormatType including DATE, https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#NumberFormat — Sankar Behera, Jul 28 '18 at 02:12
row1.add(cell); followed by this link https://stackoverflow.com/questions/37986171/google-sheet-api-v4java-append-date-in-cells. — Sankar Behera, Jul 28 '18 at 02:25

score 0 · Answer 2 · answered Sep 26 '18 at 09:44

Add row to google sheet, work in C#, Google.Api.Sheet v4

public void SaveAccount(String userName, String password) {

        GetCredential();

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

        // Define one row
        List<IList<Object>> values = new List<IList<object>>();

        IList<Object> obj = new List<Object>();
        obj.Add(userName);
        obj.Add(password);

        values.Add(obj);

        // Add Batch 
        SpreadsheetsResource.ValuesResource.AppendRequest request =
           service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, idHBusAccount, rangeHBusAccount);
        request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
        request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
        var response = request.Execute();

        // Console.WriteLine(response.ToString());

    }

score 0 · Answer 3 · answered Sep 14 '21 at 23:53

'OBTIENE EL ID DEL ARCHIVO REGISTRO EN INTERNET ID()

        ' If modifying these scopes, delete your previously saved credentials
        ' at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        Dim Scopes As String = SheetsService.Scope.SpreadsheetsReadonly
        Dim ApplicationName As String = "Google Sheets API .NET Quickstart"

        Dim credential2 As UserCredential
        Dim stream2 = New FileStream(My.Application.Info.DirectoryPath + "\client_secret.json", FileMode.Open, FileAccess.Read)
        Dim credPath As String = My.Application.Info.DirectoryPath + "\"
        credential2 = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream2).Secrets, {Scopes}, "user", CancellationToken.None, New FileDataStore(credPath, True)).Result
        stream2.Close()

        ' Create Google Sheets API service.
        Dim Service = New SheetsService(New BaseClientService.Initializer() With {.HttpClientInitializer = credential2, .ApplicationName = ApplicationName})

        Dim ranges As IList
        Dim includeGridData As Boolean = False
        Dim request2 As SpreadsheetsResource.GetRequest = Service.Spreadsheets.Get(IDREGISTRO)
        request2.Ranges = ranges
        request2.IncludeGridData = includeGridData

        'TODOS LOS DATOS
        Dim response As Data.Spreadsheet = request2.Execute()
        Dim totalrows = response.Sheets(0).Properties.GridProperties.RowCount
        Dim totalcolumsn = response.Sheets(0).Properties.GridProperties.ColumnCount
        Dim Hoja = response.Sheets(0).Properties.Title
        Dim Range As String = Hoja + "!A2:" + "L" + totalrows.ToString

        ' Define request parameters.
        Dim request As SpreadsheetsResource.ValuesResource.GetRequest = Service.Spreadsheets.Values.Get(IDREGISTRO, Range)
        Dim response2 As ValueRange = request.Execute()
        Dim values = response2.Values
        ' The ID of the spreadsheet to add row.
        Dim spreadsheetId As String = IDREGISTRO


        Dim r1 As String = Hoja & "!A" & totalrows & ":L" & totalrows

        ' Define one row
        Dim values2 As List(Of IList(Of Object)) = New List(Of IList(Of Object))()
        Dim obj As IList(Of Object) = New List(Of Object)()
        obj.Add(dt)
        obj.Add(TextBox1.Text)
        obj.Add(TextBox2.Text)
        obj.Add(TextBox6.Text)
        obj.Add(TextBox7.Text)
        obj.Add(TextBox3.Text)
        obj.Add(TextBox5.Text)
        obj.Add(TextBox4.Text)
        obj.Add(combobox1.Text)
        obj.Add(DIRMAC)
        obj.Add("NO")
        obj.Add(My.Application.Info.AssemblyName)
        values2.Add(obj)

        Dim valueInputOption As SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW
        Dim insertDataOption As SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS
        Dim request4 As SpreadsheetsResource.ValuesResource.AppendRequest = Service.Spreadsheets.Values.Append(New ValueRange With {.Values = values2}, spreadsheetId, r1)
        request4.ValueInputOption = valueInputOption
        request4.InsertDataOption = insertDataOption
        Dim response4 = request4.Execute()

English is preferred if you can. – narcis dpr Sep 15 '21 at 03:26 — narcis dpr, Sep 15 '21 at 03:26

Google Sheet API v4 insert new row

3 Answers3