Replace Row Data by Find Text | Kodular/App Inventor

Replace Row Data by Find Text

A Guide to Using the ReplaceRow Block in Kodular/App Inventor

Introduction

The "ReplaceRow" functionality in Kodular and App Inventor allows you to find and update a specific row in Google Sheets based on a unique column value. This is extremely useful when you need to update records without knowing the exact row number.

How ReplaceRow Works

The ReplaceRow method searches for a specific value in a designated unique column, and when found, replaces the entire row with new data.

Column A (Unique)

Column B

Column C

Column D

Hello

Data B1

Data C1

Data D1

World

Data B2

Data C2

Data D2

Replace row where Column A = "Hello" with new values: ["A10", "B10", "C10", "D10"]

Column A (Unique)

Column B

Column C

Column D

A10

B10

C10

D10

World

Data B2

Data C2

Data D2

Block Components

Event Trigger

This block defines when the replace action should occur. In your case, it's triggered when a button named "Replace_Row" is clicked.

when Replace_Row.Click do call GoogleSheet1.ReplaceRow

Search Parameters

These blocks specify which column to search in and what value to look for.

uniqueColumn: "Column A" findText: "Hello"

Update Data

This block provides the new values that will replace the existing row data.

updateData: make a list "A10" "B10" "C10" "D10"

Response Handler

This block handles the response from Google Sheets after the replace operation is completed.

when GoogleSheet1.RowReplaced code, msg, rowNumber do set Label1.Text to code set Label2.Text to msg set Label3.Text to rowNumber

Step-by-Step Implementation

1

Set Up Google Sheets Component

Ensure your Google Sheets component is properly configured with your spreadsheet's API key and Sheet ID. This connection is essential for the ReplaceRow functionality to work.

2

Create the Replace Button

Add a button to your app's interface and name it "Replace_Row". This button will trigger the replace action when clicked.

3

Define the Replace Action

Use the ReplaceRow method from the Google Sheets component. Specify the unique column, search text, and provide the new values as a list.

GoogleSheet1.ReplaceRow( uniqueColumn: "Column A", findText: "Hello", updateData: list "A10" "B10" "C10" "D10" )

4

Handle the Response

Use the RowReplaced event to capture the response from Google Sheets. This event provides a response code, message, and the row number that was updated.

when GoogleSheet1.RowReplaced( code, msg, rowNumber ) do Label1.Text = code Label2.Text = msg Label3.Text = rowNumber

ReplaceRow vs UpdateRow

Understanding the difference between ReplaceRow and UpdateRow is important for choosing the right method for your app.

ReplaceRow

• Searches for a value in a specific column
• Replaces the entire row with new data
• Doesn't require knowing the row number
• Useful for updating records based on unique identifiers

UpdateRow

• Requires knowing the exact row number
• Updates the entire row with new data
• Faster as it doesn't require searching
• Useful when you know the exact position of the data

Tips for Success

• Choose a truly unique column for searching to avoid updating multiple rows
• Always test your Google Sheets connection before implementing the replace functionality
• Make sure your app has the necessary permissions to edit the Google Sheet
• Use error handling to manage cases where the search text isn't found
• Consider adding a confirmation dialog before replacing important data
• Ensure the updateData list has the correct number of elements for your sheet columns

Common Response Codes

When Google Sheets processes your replace request, it returns a response code that indicates whether the operation was successful or if there was an error.

- 200: Success - The row was replaced successfully
- 400: Bad Request - The request was malformed or missing parameters
- 404: Not Found - The search text wasn't found in the unique column
- 401: Unauthorized - API key or permissions issue
- 403: Forbidden - Sheet access denied

Your app should handle these responses appropriately to provide feedback to the user.

Created for Kodular and App Inventor Developers

This guide provides a comprehensive overview of the ReplaceRow functionality. For more detailed information, refer to the official documentation.